String replacement in SQL Server using the contents of another table (e.g. update table1 set value = replace(table1.value, table2.val1, table2.val2))

Question

I have a SQL Server table that contains postal addresses. In preparation for mailing, I need to do a number of string replacements to conform with USPS preferences ("Avenue" becomes "Ave", for example).

To save me the trouble of enumerating all of the replacements, I have the label/abbreviation pairs saved in a two-column table. Is there an elegant way to pass each of those as parameters in the Replace function inside an update statement?

The lookup table looks like this:

CREATE TABLE addressLookup (label varchar(50),abbreviation varchar(20))
INSERT INTO addressLookup (label,abbreviation)
SELECT 'Avenue','Ave' UNION
SELECT 'Boulevard','Blvd' UNION
SELECT 'Drive','Dr' UNION
SELECT 'Lane','Ln' UNION
SELECT 'Street','St' UNION
SELECT 'First','1st' UNION
SELECT 'Second','2nd' UNION
SELECT 'Third','3rd' UNION
SELECT 'Fourth','4th' UNION
SELECT 'Fifth','5th' UNION
SELECT 'Sixth','6th' UNION
SELECT 'Seventh','7th' UNION
SELECT 'Eighth','8th' UNION
SELECT 'Ninth','9th' UNION
SELECT 'Tenth','10th' UNION
SELECT 'Eleventh','11th' UNION
SELECT 'Twelfth','12th' UNION
SELECT 'Apartment','Apt' UNION
SELECT 'Apartments','Apts' UNION
SELECT 'Floor','Fl' UNION
SELECT 'Room','Rm' UNION
SELECT 'Suite','Ste' UNION
SELECT 'Po Box','PO Box' UNION
SELECT 'P O Box','PO Box' UNION
SELECT 'P o Box','PO Box' UNION
SELECT 'Rural Route','RR' UNION
SELECT 'R Rte','RR' UNION
SELECT 'Rr','RR'

And this would be an example of the data being operated on (I know it's sloppy, but this is just an example):

CREATE TABLE addresses (userid int PRIMARY KEY, address1 varchar(50), address2 varchar(50), address3 varchar(50), city varchar(50), state varchar(50), zip varchar(50))
INSERT INTO addresses (userid,address1,address2,address3,city,state,zip)
SELECT 10,'Indiana University','123 University Lane','Campus Box 123','Bloomington','IN','47405'

The problem with the update is that an arbitrary number of records from the addressLookup table could match the contents of the address table. I guess I could implement a recursive stored procedure to do the job, but I was hoping someone would have a better, more elegant solution.

Edit

Just to clarify, the Addresses table has already been populated (with several million records). I was just trying to anticipate anyone who might complain about needing real data to test a solution.

Answer 1

You could probably do this using a CURSOR. but using Sql Server 2005 CTE you can try this.

Here is a full working sample:

DECLARE @addressLookup TABLE (label varchar(50),abbreviation varchar(20))
INSERT INTO @addressLookup (label,abbreviation)
SELECT 'Avenue','Ave' UNION
SELECT 'Boulevard','Blvd' UNION
SELECT 'Drive','Dr' UNION
SELECT 'Lane','Ln' UNION
SELECT 'Street','St' UNION
SELECT 'First','1st' UNION
SELECT 'Second','2nd' UNION
SELECT 'Third','3rd' UNION
SELECT 'Fourth','4th' UNION
SELECT 'Fifth','5th' UNION
SELECT 'Sixth','6th' UNION
SELECT 'Seventh','7th' UNION
SELECT 'Eighth','8th' UNION
SELECT 'Ninth','9th' UNION
SELECT 'Tenth','10th' UNION
SELECT 'Eleventh','11th' UNION
SELECT 'Twelfth','12th' UNION
SELECT 'Apartment','Apt' UNION
SELECT 'Apartments','Apts' UNION
SELECT 'Floor','Fl' UNION
SELECT 'Room','Rm' UNION
SELECT 'Suite','Ste' UNION
SELECT 'Po Box','PO Box' UNION
SELECT 'P O Box','PO Box' UNION
SELECT 'P o Box','PO Box' UNION
SELECT 'Rural Route','RR' UNION
SELECT 'R Rte','RR' UNION
SELECT 'Rr','RR'


DECLARE @addresses TABLE (userid int PRIMARY KEY, address1 varchar(50), address2 varchar(50), address3 varchar(50), city varchar(50), state varchar(50), zip varchar(50))
INSERT INTO @addresses (userid,address1,address2,address3,city,state,zip)
SELECT 10,'Indiana University','123 University Lane Suite','Campus Box 123','Bloomington','IN','47405'

;WITH CTE AS(
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY label) RowID
        FROM    @addressLookup
),
CTERep AS(
        SELECT  CTE.*,
                userid,
                REPLACE(address1, CTE.label,CTE.abbreviation) address1,
                REPLACE(address2, CTE.label,CTE.abbreviation) address2,
                REPLACE(address3, CTE.label,CTE.abbreviation) address3,
                REPLACE(city, CTE.label,CTE.abbreviation) city,
                REPLACE(state, CTE.label,CTE.abbreviation) state,
                REPLACE(zip, CTE.label,CTE.abbreviation) zip,
                1 AS Depth
        FROM    CTE, @addresses a
        WHERE   RowID = 1
        UNION ALL
        SELECT  CTE.*,
                CTERep.userid,
                REPLACE(CTERep.address1, CTE.label,CTE.abbreviation) address1,
                REPLACE(CTERep.address2, CTE.label,CTE.abbreviation) address2,
                REPLACE(CTERep.address3, CTE.label,CTE.abbreviation) address3,
                REPLACE(CTERep.city, CTE.label,CTE.abbreviation) city,
                REPLACE(CTERep.state, CTE.label,CTE.abbreviation) state,
                REPLACE(CTERep.zip, CTE.label,CTE.abbreviation) zip,
                CTERep.Depth + 1
        FROM    CTE INNER JOIN
                CTERep ON CTE.RowID = CTERep.RowID + 1
)
SELECT  userid,
        address1,
        address2,
        address3,
        city,
        state,
        zip
FROM    CTERep
WHERE   Depth = (SELECT COUNT(*) FROM @addressLookup)