splitting a string into three fields
Moderators: chulett, rschirm, roy
splitting a string into three fields
I have a fixed width file.
One of the column is City_state_Zip length 30
i need to split the column into three separate columns city,state& zip.
how do i achieve this?
i tried with field function with space as a delimiter but did not help me because my data looks like this .The city has two values with space .
SPARTANBURG SC 29303
S PASADENA FL 33707
LA GRANGE NC 28551
One more important thing , after the complete city,state and zip data has spaces . So it is not something like i pick the last 5characters which could be my zipcode.
can anyone help me with this
Thanks
Paddu
One of the column is City_state_Zip length 30
i need to split the column into three separate columns city,state& zip.
how do i achieve this?
i tried with field function with space as a delimiter but did not help me because my data looks like this .The city has two values with space .
SPARTANBURG SC 29303
S PASADENA FL 33707
LA GRANGE NC 28551
One more important thing , after the complete city,state and zip data has spaces . So it is not something like i pick the last 5characters which could be my zipcode.
can anyone help me with this
Thanks
Paddu
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
This post should resolve your issue.
1.Just you need to Trim that field.
2. Field(Input.field,' ',1) and Field(Input.field,' ',2) should be concatenated with space in between.
3. Field(Input.field,' ',3) will be your state and
4. Field(Input.field,' ',4) will be your Zip.
Code: Select all
http://www.dsxchange.com/viewtopic.php?t=108018&highlight=split
2. Field(Input.field,' ',1) and Field(Input.field,' ',2) should be concatenated with space in between.
3. Field(Input.field,' ',3) will be your state and
4. Field(Input.field,' ',4) will be your Zip.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Apply a TRIMB() before anything else. Stick the result in a stage variable.
Use the Field() function to get the state and zip. For example zip might contain five or nine characters. Beware that the city name may have spaces in it, so you need to know how many fields there are in the string, and extract the last and second last for state and zip respectively. If you extract zip then state using Field() functions, you can use Left(TheString, Col1()) for city immediately afterwards.
Use the Field() function to get the state and zip. For example zip might contain five or nine characters. Beware that the city name may have spaces in it, so you need to know how many fields there are in the string, and extract the last and second last for state and zip respectively. If you extract zip then state using Field() functions, you can use Left(TheString, Col1()) for city immediately afterwards.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
as suggested i did used two stage variables
1. sv1-Trim(input.field)
2.vcount-Dcount(sv1,' ')
transformer logic for
City-if vcount>3 Then Field(sv1,' ',1):' ':Field(sv1,' ',2) Else Field(sv1,' ',1)
state-if vcount>3 Then Field(sv1,' ',3) Else Field(sv1,' ',2)
zip- if vcount>3 Then Field(sv1,' ',4) Else Field(sv1,' ',3)
the result is
for some three delimited fields city and state is in the City column for the rest is got separated correctly.
same thing for 4 delimited values .for some it is fine and rest not.
result ( i wish i could send exactly what i get .how do i type columns with the data?)
city State zip
TAMPA FL 33602
TAMPA FL 33602
S PASADENA FL
S PASADENA FL 33707
[code][/code][/i]
i see taht the vcount values were not right
vcount values in the order of the result
4
3
1
4
1. sv1-Trim(input.field)
2.vcount-Dcount(sv1,' ')
transformer logic for
City-if vcount>3 Then Field(sv1,' ',1):' ':Field(sv1,' ',2) Else Field(sv1,' ',1)
state-if vcount>3 Then Field(sv1,' ',3) Else Field(sv1,' ',2)
zip- if vcount>3 Then Field(sv1,' ',4) Else Field(sv1,' ',3)
the result is
for some three delimited fields city and state is in the City column for the rest is got separated correctly.
same thing for 4 delimited values .for some it is fine and rest not.
result ( i wish i could send exactly what i get .how do i type columns with the data?)
city State zip
TAMPA FL 33602
TAMPA FL 33602
S PASADENA FL
S PASADENA FL 33707
[code][/code][/i]
i see taht the vcount values were not right
vcount values in the order of the result
4
3
1
4
i don't mean that there are some records with single delimiter.
i do not know why for one record the Dcount is 4 and for the other 3. they are exactly same data . Am i missing something.
before triming the field there were two spaces between the state and zip . after the trim there is one space between the state and zip.
which means if my data looks like "TAMPA FL 33602" the Dcount function should return 3 (space as delimiter). NO idea why for the other record the Dcount function returned 4
anyways i will post couple of source data tomorrow .
Thanks
paddu
i do not know why for one record the Dcount is 4 and for the other 3. they are exactly same data . Am i missing something.
before triming the field there were two spaces between the state and zip . after the trim there is one space between the state and zip.
which means if my data looks like "TAMPA FL 33602" the Dcount function should return 3 (space as delimiter). NO idea why for the other record the Dcount function returned 4
anyways i will post couple of source data tomorrow .
Thanks
paddu