Member_2_2484401
asked on
Parse 1 column into 3 columns using DB2 SQL
Using DB2 for z/OS 10, I'm have a column (ADDRESS3) in a table that contains city, state, and zipcode all in one column. But, the formatting is somewhat "free-form". (see code below)
I can successfully parse the city by taking everything before the comma. Using only DB2 SQL, how would I parse out the state and zipcode?
Thanks!
I can successfully parse the city by taking everything before the comma. Using only DB2 SQL, how would I parse out the state and zipcode?
SELECT ADDRESS3,
TRIM(SUBSTR(ADDRESS3,1,LOCATE(',', ADDRESS3)-1)) AS CITY
FROM MYTABLE
ADDRESS3 CITY
---------------------------------- -------------
GRANADA HILLS ,CA 91344 GRANADA HILLS
SIMI VALLEY, CA 93065 SIMI VALLEY
BUENA PARK ,CA 90621 BUENA PARK
SHERMAN OAKS, CA 91423 SHERMAN OAKS
GLENDALE ,CA 91203-2089 GLENDALE
VENTURA ,CA 93002 VENTURA
HAWTHORNE ,CA 90250 HAWTHORNE
PASADENA ,CA 91185-2594 PASADENA
BEVERLY HILLS ,CA 90211 BEVERLY HILLS
ARCADIA ,CA 91007 ARCADIA
WALNUT CREEK ,CA 94596 WALNUT CREEK
INDEPENDANCE ,CA 93526 INDEPENDANCE
LOS ANGELES ,CA 90017 LOS ANGELES
Ultimately, I'd like the query results to look like this:CITY STATE ZIP
------------- ----- -----
GRANADA HILLS CA 91344
SIMI VALLEY CA 93065
BUENA PARK CA 90621
SHERMAN OAKS CA 91423
GLENDALE CA 91203
VENTURA CA 93002
HAWTHORNE CA 90250
PASADENA CA 91185
...
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER