SQL Server 中使用另一个表的内容进行字符串替换（例如 update table1 set value = Replace(table1.value, table2.val1, table2.val2)）

我有一个包含邮政地址的 SQL Server 表。在准备邮寄时，我需要进行多次字符串替换以符合 USPS 偏好（例如，“Avenue”变为“Ave”）。

为了省去枚举所有替换的麻烦，我将标签/缩写对保存在两列表中。是否有一种优雅的方法可以将每个参数作为更新语句内的 Replace 函数中的参数传递？

查找表如下所示：

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'

这将是正在操作的数据的示例（我知道它很草率，但这只是一个示例）：

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'

更新的问题在于，addressLookup 表中的任意数量的记录都可能与地址表的内容相匹配。我想我可以实现一个递归存储过程来完成这项工作，但我希望有人能有一个更好、更优雅的解决方案。

Edit

需要澄清的是，Addresses 表已被填充（包含数百万条记录）。我只是试图预测有人可能会抱怨需要真实数据来测试解决方案。

你可能可以使用CURSOR。但使用 Sql Server 2005 CTE 你可以尝试这个。

这里有一个full工作样本：

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)