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

Note that there are some explanatory texts on larger screens.

plurals
  1. POString replacement in SQL Server using the contents of another table (e.g. update table1 set value = replace(table1.value, table2.val1, table2.val2))
    text
    copied!<p>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).</p> <p>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?</p> <p>The lookup table looks like this:</p> <pre><code>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'</code></pre> <p>And this would be an example of the data being operated on (I know it's sloppy, but this is just an example):</p> <pre><code> 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' </code></pre> <p>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.</p> <h3>Edit</h3> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload