StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You could probably do this using a <em>CURSOR</em>. but using Sql Server 2005 CTE you can try this.</p> <p><em>Here is a <strong>full</strong> working sample:</em></p> <pre><code>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) </code></pre>

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