If a comma is your delimiter, then you can use the SUBSTR and INSTR functions to parse out the components. The...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.