问题描述
我有一个包含邮政地址的 SQL Server 表.在准备邮寄时，我需要进行一些字符串替换以符合 USPS 偏好(例如，Avenue"变成Ave").
为了省去枚举所有替换的麻烦，我将标签/缩写对保存在一个两列表中.是否有一种优雅的方法可以将每个作为参数传递到更新语句中的 Replace 函数中?
查找表如下所示:
CREATE TABLE addressLookup(标签varchar(50)，缩写varchar(20))INSERT INTO 地址查找(标签，缩写)选择大道"，大道"联合选择大道"，大道"联合选择驱动器"，联合博士"选择'车道'，'Ln'联合选择街道"，圣"联合选择第一个"、第一个"联合选择第二个"，第二个"联合选择第三个"、第三个"联合选择第四个"、第四个"联合选择第五个"、第五个"联合选择第六个"、第六个"联合选择第七个"、第七个"联合选择第八个"、第八个"联合选择第九个"、第九个"联合选择第十个"、第十个"联合选择第十一个"、第 11 个"联合选择第十二个"、第十二个"联合选择公寓"，公寓"联合选择公寓"，公寓"联合选择 '楼层', 'Fl' 联合选择房间"，房间"联合选择套房"，Ste"联合选择邮政信箱"、邮政信箱"联合选择邮政信箱"、邮政信箱"联合选择邮政信箱"、邮政信箱"联合SELECT '乡村路线','RR' UNIONSELECT 'R Rte','RR' UNIONSELECT 'Rr','RR'
这将是正在操作的数据的一个示例(我知道这很草率，但这只是一个示例):
<预><代码>创建表地址(userid int PRIMARY KEY、address1 varchar(50)、address2 varchar(50)、address3 varchar(50)、city varchar(50)、state varchar(50)、zip varchar(50))INSERT INTO 地址(用户 ID、地址 1、地址 2、地址 3、城市、州、邮编)选择 10,'印第安纳大学','大学巷 123 号','校园框 123','布卢明顿','IN','47405'更新的问题在于 addressLookup 表中任意数量的记录可能与地址表的内容匹配.我想我可以实现一个递归存储过程来完成这项工作，但我希望有人能有一个更好、更优雅的解决方案.
编辑
澄清一下，地址表已经被填充(有几百万条记录).我只是想预料到有人可能会抱怨需要真实数据来测试解决方案.
您可能可以使用 CURSOR 来做到这一点.但是使用 Sql Server 2005 CTE 你可以试试这个.
这是一个完整工作示例:
DECLARE @addressLookup TABLE(标签varchar(50)，缩写varchar(20))INSERT INTO @addressLookup(标签，缩写)选择大道"，大道"联合选择大道"，大道"联合选择驱动器"，联合博士"选择'车道'，'Ln'联合选择街道"，圣"联合选择第一个"、第一个"联合选择第二个"，第二个"联合选择第三个"、第三个"联合选择第四个"、第四个"联合选择第五个"、第五个"联合选择第六个"、第六个"联合选择第七个"、第七个"联合选择第八个"、第八个"联合选择第九个"、第九个"联合选择第十个"、第十个"联合选择第十一个"、第 11 个"联合选择第十二个"、第十二个"联合选择公寓"，公寓"联合选择公寓"，公寓"联合选择 '楼层', 'Fl' 联合选择房间"，房间"联合选择套房"，Ste"联合选择邮政信箱"、邮政信箱"联合选择邮政信箱"、邮政信箱"联合选择邮政信箱"、邮政信箱"联合SELECT '乡村路线','RR' UNIONSELECT 'R Rte','RR' UNION选择'Rr'，'RR'声明@addresses 表(用户ID 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,'印第安纳大学','123 University Lane Suite','Campus Box 123','Bloomington','IN','47405';与 CTE 为 (选择 *，ROW_NUMBER() OVER(ORDER BY 标签)RowID从@addressLookup),CTERep AS(选择 CTE.*,用户身份，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) 城市，REPLACE(state, CTE.label,CTE.abbreviation) 状态，REPLACE(zip, CTE.label,CTE.abbreviation) zip,1 AS 深度FROM CTE，@addresses aWHERE RowID = 1联合所有选择 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) 城市，REPLACE(CTERep.state, CTE.label,CTE.abbreviation) 状态，REPLACE(CTERep.zip, CTE.label,CTE.abbreviation) zip,CTERep.Depth + 1从 CTE 内连接CTE.RowID 上的 CTERep = CTERep.RowID + 1)选择用户名，地址1，地址2，地址3，城市，状态，压缩从 CTERepWHERE Depth = (SELECT COUNT(*) FROM @addressLookup)
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.
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)
