Parsing string from a single column to multiple columns (address data)

0 pts.
Tags:
SQL
I have TEMP_TBL which I am using as a staging area to clean up data before inserting it into 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 ADDRESS column. Please help -- I have not been able to get this to work.
ASKED: May 3, 2006  10:22 AM
UPDATED: May 4, 2006  10:18 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello,
In Oracle exists a function ‘INSTR’ which gives you the position of a string in which some delimiter appears.
The format is INSTR(STRING,DELIMITER,STARTING_POSITION,OCCURENCE)

I think you can use it with your address issue.

Example:

select substr(address,1,instr(address,’,',1,1)-1) street_1,
substr(address,instr(address,’,',1,1)+1,instr(address,’,',1,2)-instr(address,’,',1,1)-1) street_2,
substr(address,instr(address,’,',1,2)+1,instr(address,’,',1,3)-instr(address,’,',1,2)-1) city,
substr(address,instr(address,’,',1,3)+1,length(address)) state
FROM original_table
where … ;

Of course this will work only when a delimiter exists.

Hope this helps.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following