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.
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).
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading