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.