Q
Problem solve Get help with specific problems with your technologies, process and projects.

The SQL REPLACE function

SQL Expert Rudy Limeback answers an Oracle user's question about the REPLACE function.

I am trying to use a REPLACE statement to replace elements of an AddressLine1 field in an attempt to remove commas, periods, hyphens, and replace '3 South ' with '3 S ' (as in 123 South Main Street to 123 S Main Street), plus 95 other things so far.

What I need to figure out is how to do a replacement of ' Road' at the end of the address to ' Rd' without changing...

an address such as '2884 N Roadrunner Pkwy' to '2884 N Rdrunner Pkwy'. To do this, I need to do something like REPLACE(' Road[ENDOFLINE]',' Rd[ENDOFLINE]'). But that obviously doesn't work.

First of all, my condolences for the onerous task of laundering addresses. This is truly one of those horrible tasks which is better performed outside of the database than with SQL (as I'm sure you already know, faced with 95 things to change).

Let's assume you're using a database system which has a REPLACE function (not all database systems do). The REPLACE you're looking for goes like this:

 UPDATE yourtable SET AddressLine1 = REPLACE(AddressLine1, ' Road', ' Rd') WHERE AddressLine1 LIKE '% Road'

This will perform the update on only those rows that end with ' Road'. Note that there is a leading wildcard for the LIKE string, but no trailing wildcard.

Of course, this will also change '123 Roadley Road' to '123 Rdley Rd'. This is because the REPLACE itself does not look only at the end of the column value, but all through it. If you are worried about these situations, then you could enhance your WHERE clause like this:

 WHERE AddressLine1 LIKE '% Road' AND DATALENGTH(Replace(AddressLine1, ' Road', ' Rd')) = DATALENGTH(AddressLine1)-2

This additional qualification uses the same REPLACE function, and compares the length of the result to the length of the original column value. Since we want only one occurrence to be replaced, which has to be at the end because of the LIKE condition, the new length can only be 2 less than the original (because the letters 'oa' have been removed).

Neat, eh?

This was last published in July 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close