Q

Parsing out components from address column

I have a TEMP_TBL which I am using as a staging area to clean up data before inserting it into an EMPLOYEE table. One of my big issues is with the column ADDRESS, which contains all address components. I need to parse out the address components into the following columns: STREET1, STREET2, CITY, STATE, ZIP. Most of the time, a comma is acting as a delimiter between the address components in the ADDRESS column.

This Content Component encountered an error
I have a TEMP_TBL which I am using as a staging area to clean up data before inserting it into an EMPLOYEE table. One of my big issues is with the column ADDRESS, which contains all address components. I need to parse out the address components into the following columns: STREET1, STREET2, CITY, STATE, ZIP. Most of the time, a comma is acting as a delimiter between the address components in the ADDRESS column. Please help -- I have not been able to get this to work.

If a comma is your delimiter, then you can use the SUBSTR and INSTR functions to parse out the components. The SUBSTR function pulls out a portion of the given string. The INSTR function returns the location in a string. So we'll use the INSTR function to find the location of the first comma, and then pull out the STREET1 value from the ADDRESS column.

SELECT SUBSTR(address,1,INSTR(address,',')-1) AS street1 FROM temp_tbl;

Above, I found the first comma and then selected the portion of ADDRESS from the first character to the character right before the first comma. To get the CITY, you will need to search for the string between the second and third commas.

SELECT SUBSTR(address,INSTR(address,',',2)+1,INSTR(address,',',3-1) AS city FROM temp_tbl;

Play around with SUBSTR and INSTR to see how they will help you solve your problem.

This was first published in May 2006

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close