如何排序WHEN THEN以获得所需替换函数的所有组合

Question

我有一个假的表:

CREATE TABLE #FAKEAddress
( AddressLin01 varchar(100))

INSERT INTO #FAKEAddress VALUES
('123 FAKE ROAD'),
('79 59th ROAD'),
('7890 COUNTY ROAD 10'),
('1768 COUNTY ROAD 14'),
('4578 CO ROAD 900'),
('15 COUNTY RD 134'),
('17902 COUNTY RD'),
('537 COUNTY ROAD')
AddressLin01
123 FAKE ROAD
79 59th ROAD
7890 COUNTY ROAD 10
1768 COUNTY ROAD 14
4578 CO ROAD 900
15 COUNTY RD 134
17902 COUNTY RD
537 COUNTY ROAD

我希望将Road的所有实例替换为RD，将County的所有实例替换为CO，无论它们是出现在字符串的中间还是末尾。以下是我到目前为止所想到的，结果不太理想:

SELECT
CASE
WHEN AddressLin01 LIKE '% ROAD' THEN REPLACE(AddressLin01, ' ROAD', ' RD')
WHEN AddressLin01 LIKE '% ROAD %' THEN REPLACE(AddressLin01, ' ROAD ', ' RD ')
WHEN AddressLin01 LIKE '% COUNTY' THEN REPLACE(AddressLin01, ', COUNTY', ' CO')
WHEN AddressLin01 LIKE '% COUNTY %' THEN REPLACE(AddressLin01, ' COUNTY ', ' CO ')
WHEN AddressLin01 LIKE '% COUNTY RD' THEN REPLACE(AddressLin01, ' COUNTY RD', ' CO RD')
WHEN AddressLin01 LIKE '% COUNTY RD %' THEN REPLACE(AddressLin01, ' COUNTY RD ', ' CO RD')
WHEN AddressLin01 LIKE '% COUNTY ROAD' THEN REPLACE(AddressLin01, ' COUNTY ROAD' , ' CO RD')
WHEN AddressLin01 LIKE '% COUNTY ROAD %' THEN REPLACE(AddressLin01, ' COUNTY ROAD ' , ' CO RD ')
ELSE AddressLin01
END AS FinalAddress
FROM #FAKEAddress
FinalAddress
123 FAKE RD
79 59th RD
7890 COUNTY RD 10
1768 COUNTY RD 14
4578 CO RD 900
15 CO RD 134
17902 CO RD
537 COUNTY RD

可以看到，它正确地处理了所有的Road实例。它还会正确处理所有的County实例，但前提是字符串中没有Road。

如果Road和County在原字符串中共存，那么什么代码将提供一个将Road替换为RD，将County替换为CO的解决方案?

Answer 1

虽然我同意这些评论，但你的问题的字面答案可能是…

只有一个THEN将被激活，第一次激活后的所有内容都被跳过。但是，CASE表达式是不需要的，如果你做一个没有必要的REPLACE，它什么也做不了。所以，把它们套起来;REPLACE(REPLACE(REPLACE(...)))

SELECT
AddressLin01,
REPLACE(
REPLACE(
AddressLin01,
' ROAD',
' RD'
),
' COUNTY',
' CO'
)
FROM
#fakeaddress

https://dbfiddle.uk/?rdbms=sqlserver_2019&小提琴= f228125c90c9699e3a7be53ddbf9023c

Answer 2

请尝试以下解决方案。

它通过XML和XQuery使用标记化。

我在表中添加了更多的行，以涵盖其他可能的场景。

/p>

-- DDL and sample data population, start
DECLARE @FAKEAddress TABLE (ID INT IDENTITY PRIMARY KEY, AddressLin01 VARCHAR(100));
INSERT INTO @FAKEAddress VALUES
('123 FAKE ROAD'),
('79 59th ROAD'),
('7890 COUNTY ROAD 10'),
('1768 COUNTY ROAD 14'),
('4578 CO ROAD 900'),
('15 COUNTY RD 134'),
('17902 COUNTY RD'),
('537 COUNTY ROAD'),
('537 MYCOUNTY ROAD'),
('537 COUNTYDAY ROAD');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
SELECT t.*
, Result = c.query('
for $x in /root/r
return if ($x/text()="ROAD") then "RD"
else if ($x/text()="COUNTY") then "CO"
else string($x)
').value('.', 'VARCHAR(100)')
FROM @FAKEAddress AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(AddressLin01, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c);

+----+---------------------+------------------+
| ID |    AddressLin01     |      Result      |
+----+---------------------+------------------+
|  1 | 123 FAKE ROAD       | 123 FAKE RD      |
|  2 | 79 59th ROAD        | 79 59th RD       |
|  3 | 7890 COUNTY ROAD 10 | 7890 CO RD 10    |
|  4 | 1768 COUNTY ROAD 14 | 1768 CO RD 14    |
|  5 | 4578 CO ROAD 900    | 4578 CO RD 900   |
|  6 | 15 COUNTY RD 134    | 15 CO RD 134     |
|  7 | 17902 COUNTY RD     | 17902 CO RD      |
|  8 | 537 COUNTY ROAD     | 537 CO RD        |
|  9 | 537 MYCOUNTY ROAD   | 537 MYCOUNTY RD  |
| 10 | 537 COUNTYDAY ROAD  | 537 COUNTYDAY RD |
+----+---------------------+------------------+