EXPERT RESPONSE
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?
|
