All Forums UDA
I_SQL_TODAY 1 post Joined 05/06
31 May 2006
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 spacesany suggestions on how I can get CITY STATE and ZIP into 3 distinct fields?Thanks in advance.

table 1 post Joined 06/06
22 Jun 2006

substr(yourString, -5, 5) for zip codesubstr(yourString, -8, 2) for state [including a space before zip]rtrim(yourString, 9) without comma between city and state for city orrtrim(yourString, 10) with comma between city and state for cityhope this helps

BBR2 96 posts Joined 12/04
22 Jun 2006

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 DERYou may use the actual column Name instead of hard coded "string"Assumption is zip code are always 5 digits.Vinay

You must sign in to leave a comment.