How to order WHEN THEN to get all combinations of desired replace fucntions-CodePudding

I have a fake table:

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

I wish to replace all instances of Road with RD, and all instances of County with CO, no matter if they occur in the middle or at the end of the string. Here is what I have come up with so far, with less than desirable results:

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

As you can see, it deals with all the Road instances properly. It also deals with all the County instances properly, but only if there isn't also a Road in the string.

What code will provide a solution to Replace Road with RD, and County with CO, if Road and County coexist in the original string?

CodePudding user response：

While I agree with the comments, a literal answer to your question could be...

Only one THEN will ever activate, everything after the first activation is skipped. But, the CASE expressions aren't needed, if you do a REPLACE that's unnecessary it just does nothing. So, nest them; REPLACE(REPLACE(REPLACE(...)))

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

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f228125c90c9699e3a7be53ddbf9023c

CodePudding user response：

Please try the following solution.

It is using tokenization via XML and XQuery.

I added a couple of more rows at the end to cover additional possible scenarios.

SQL

-- 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);

Output

 ---- --------------------- ------------------ 
| 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 |
 ---- --------------------- ------------------