splitting a string into three fields

[paddu]

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

[us1aslam1us]

This post should resolve your issue.

Code: Select all

http://www.dsxchange.com/viewtopic.php?t=108018&highlight=split

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.

[kumar_s]

After triming the data, you need to COUNT the number of delimiter. If you have 4, you need to follow the given example.
First two field will be the state. Else go with 1,2,3 as three different fields.

[ray.wurlod]

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.

[paddu]

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

[kumar_s]

You need to check the input for that particular record or post it here for analysis.
So do you mean, that there are some records with single delimiter??

[paddu]

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

[kumar_s]

Have the input in coloumn, and the Field after TRIM in another column and the count of the Delimiter in other column. This way you can easily find the actual issue.

[paddu]

i did exactly the same way .

Field ,Trim(Field),vcount(count of delimiter),city,state,zip

[kumar_s]

Could you post the same here.

[paddu]

Actual Value,Trim(field),vcount,city,state,zip

'S PASADENA FL 33707 ','S PASADENA FL 33707',1,'S','PASADENA','FL'


the true zipcode is missing but for some records it is fine.

to understand easily ,i used quotes to differentiate the acutal length of the data and comma to separate the fields .

[paddu]

i do not know when i posted the actual value with the spaces after the zipcode in the first field ,the previous post removed the spaces.

how can i post the data with spaces. what option should i use?

[kumar_s]

Hope you are using single space " " to check the count of delimiter.

[paddu]

kumar ,

i need to tell one more thing

In the actual data there is one space between city and state and two spaces between state and zipcode . After Trim function there is one space between the city,state and zip.

so i used only single space as delimiter

[chulett]

Code tags would preserve spaces and formatting. Anything else gets you an aligned and trimmed display.