P**** City State Zip

UDA

P**** City State Zip

Hello Folks,

We are getting City State ZIP information all in one field - like this

'BAYTOWN TX....................77520'

or

'BAY CITY.......TX.............77414'

where '.....' means spaces

any suggestions on how I can get CITY STATE and ZIP into 3 distinct fields?

Thanks in advance.

2 REPLIES
N/A

Re: P**** City State Zip

substr(yourString, -5, 5) for zip code
substr(yourString, -8, 2) for state [including a space before zip]
rtrim(yourString, 9) without comma between city and state for city
or
rtrim(yourString, 10) with comma between city and state for city

hope this helps
N/A

Re: P**** City State Zip

Use this

SEL
CITY
,STATE
,ZIP
FROM
(SEL 'BAY CITY TX 77414' AS Str
,CHARACTER_LENGTH(Str) AS MAX_CHR
,CAST((TRIM(SUBSTRING(STR FROM (MAX_CHR-5) FOR MAX_CHR)))AS INT) AS ZIP
,TRIM(BOTH FROM SUBSTRING(STR FROM 1 FOR (MAX_CHR-5))) AS CITY_ST
,CHARACTER_LENGTH(TRIM(SUBSTRING(STR FROM 1 FOR (MAX_CHR-5)))) AS CITY_ST_LEN

,TRIM(SUBSTRING(CITY_ST FROM (CITY_ST_LEN -2) FOR CITY_ST_LEN)) AS STATE
,CHARACTER_LENGTH(TRIM(SUBSTRING(CITY_ST FROM 1 FOR (CITY_ST_LEN -2)))) AS CITY_LEN

,TRIM(SUBSTRING(CITY_ST FROM 1 FOR CITY_LEN)) AS CITY) AS DER

You may use the actual column Name instead of hard coded "string"
Assumption is zip code are always 5 digits.

Vinay