Ask the Expert

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. Please help -- I have not been able to get this to work.

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: