Ask The Oracle Expert: Questions & Answers

The SQL REPLACE function

The SQL REPLACE function

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

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?