Data parsing

[jose1024]

I have a column that contains city, state zip data. I need to place city and state in one column and zip in another. The field is 25 characters in length and obviously, the city varies in length within the field. I am using Transact SQL in SQL Server 2000 Query Analyzer. Any suggestions?

Examples:

current setup:
CityStateZip
ANYTOWN, NY 11123

desired result:
CityState Zip
ANYTOWN, NY 11123

 

[SQLSister]

Are your zip codes 5 digits or 9 digits or a mix?

Questions about posting. See faq183-874

 

[jose1024]

Just 5 digits

 

[SQLSister]

Use the right function to take the last five digits
YOu may also need to make sure that the last five digits are all numeric, wouldn't want totmove the last fice letters if they didn;t put in a zip code.

I'd copy the last five digits tot he new field first. Look at it make sure everything is good, and then use the substring function or left function (with length - 5 as the length of the string) to update the old field to remove the last five characters.

Questions about posting. See faq183-874

 

[jose1024]

I understand about the copying of the zip to new field, but I am not sure how to delete it from its existing location. If I use the left or substring function, how do I determine the length? You said to use 5 as my length, could you expound on why to use 5?

 

[dbomrrsm]

UPDATE YOURTABLE
SET ZIP = RIGHT(CityStateZip,5)

This will put the right most 5 charachters from CityStateZip into the ZIP field - this is why SQLSister said beware that all fields have a 5 digit zip code in them otherwise you will get the rightmost five of whatever is there.

Then

UPDATE YOURTABLE
SET CityState = SELECT LEFT(CityStateZip,(LEN(CityStateZip)-PATINDEX('% %',REVERSE(CityStateZip)-1)))

This works out the length of the field say 25 then works out the first occurence of the space before the zip code say 6 then takes one from the other and updates the field to leave wou with the left 19 of the original CityStateZip field which should be all less the zip code.

Worht testing this in a select first to make sure its ok.

DBomrrsm

 

[jose1024]

Thank you, I will give it a shot.