Splitting a field (column) into variable parts

[groleau]

This is a tricky one. It could be done (not easily) with
regular expressions, but I'd like to do it within Jet SQL.
The data is already in a VERY long table, and I'd rather
not call it into a recordset and loop through it.

The field contains zero to seven subfields, with one-char
delimiters. Fields 1, 3, 4, 5, 6 are either two characters
or missing. Delimiters are missing after the last present
field. Field 2 is missing or 1-48 chars. Field seven is
zero to eighty chars.

Delimiters can be different in different rows. If the
field has three or more characters, the third is the
delimiter for the field.

What I need to do is turn this single column into four:

First: the first subfield, if any, else null.
Second: the second, if any, else null.
Third, subfields 3-6 with the delimiter standardized
to a space. Null if all missing or only delimiters..
Fourth, subfield seven if any else null.

If the table were shorter, I could export to CSV,
load into excel, add a column eight placeholder
and dump back into Access, .....

--
Wes Groleau

http://www.Parkview.com/

 

[stevexff]

Can you post some sample data, about 10-20 lines, prefereably with missing fields and different delimiters? I'm having a bit of trouble visualising it.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[groleau]

One solution is found in

http://www.tek-tips.com/viewthread.cfm?qid=1347547

But here's a sample. "service" is split into the other four columns. Sorry about the alignment--pasted from tab-delimited.

service	Codeset	Code	Mods	Remark

HC:L0120	HC	L0120		
HC|L0120	HC	L0120		
HC>L0120	HC	L0120		
HC>K0738>RR	HC	K0738	RR	
HC>K0195>RR>KJ	HC	K0195	RR KJ	
HC>K0007>RR>KH	HC	K0007	RR KH	
HC>K0007>RR>KI	HC	K0007	RR KI	
HC>K0007>RR>KJ	HC	K0007	RR KJ	
HC>K0001>MS	HC	K0001	MS	
HC>K0001>MS>BR	HC	K0001	MS BR	
HC>K0001>RR	HC	K0001	RR	
HC>K0001>RR>KI>GL	HC	K0001	RR KI GL	
HC>K0001>RR>KJ>GL	HC	K0001	RR KJ GL	
HC>J9999	HC	J9999		
HC>J9999>25	HC	J9999	25
HC:J9390	HC	J9390	
HC|J9370	HC	J9370	
HC:J9360	HC	J9360

--
Wes Groleau

http://www.Parkview.com/

 

[CautionMP]

groleau,
Are you sure you want to do it in JET SQL? If you do I think you will want to do it in several steps or you'll end up with something twice as long as this (I only got halfway through the [tt]Mods[/tt] column and I can no longer keep track of the parenthesis!)
[tt]SELECT [tbl707-1360669].Field1,
IIf(Len([Field1])<>0,Left([Field1],2),"") AS Codeset,
IIf(Len([Field1])<>0,IIf(Len([Field1])-Len(Replace(Replace(Replace([field1],">",""),"|",""),":",""))=1,Mid([Field1],4),IIf(Len([Field1])-Len(Replace(Replace(Replace([field1],">",""),"|",""),":",""))>1,Mid([Field1],4,InStr(4,Replace(Replace([field1],">",":"),"|",":"),":")-4),""))) AS Code,
IIf(Len([Field1])<>0,IIf(Len([Field1])-Len(Replace(Replace(Replace([field1],">",""),"|",""),":",""))=2,Mid([Field1],Len(

)+5),IIf(Len([Field1])-Len(Replace(Replace(Replace([field1],">",""),"|",""),":",""))>2,Mid([Field1],Len([Code])+5,InStr(Len([Code])+5,Replace(Replace([field1],">",":"),"|",":"),":")-4),""))) AS Mods
FROM [tbl707-1360669];[/tt]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[groleau]

"Are you sure you want to do it in JET SQL?"

No. I should have been more clear. SQL wasn't the goal--
the goal was avoiding looping in VBA through 800,000
records. The first solution was to do the looping in SQL
but have the conversion in VBA. That was still too slow,
so instead I SELECTed DISTINCT values, converting those,
then JOINing the converted values back in.

--
Wes Groleau

http://www.Parkview.com/