下面是我用来重新创建示例表和NewAddress的第二列（稍后将用于比较）的代码：

DROP TABLE IF EXISTS #Address
CREATE TABLE #Address (Address VARCHAR(64), NewAddress VARCHAR(64))
INSERT INTO #Address (Address, NewAddress)
VALUES ('UNIT 7 MID RISKBEITH ROADHOWWOODJOHNSTONE', NULL), ('BELLWAY SITE HUTSCOLINTRAIVE CRESCENTBISHOPTON', NULL)

DROP TABLE IF EXISTS #Town
CREATE TABLE #Town (ID INT, Description VARCHAR(25))
INSERT INTO #Town (ID, Description)
VALUES (1, 'JOHNSTONE'), (2, 'BISHOPTOWN'), (3, 'ABERDEEN'), (4, 'GLASGOW')

然后我使用这个代码获取原始地址，并使用'%'和城镇的concat对城镇列表进行左连接，以匹配地址的结尾与城镇名称之一完全相同的地方。我做了一些工作来找到城镇名称的长度，然后使用这个代码缩短地址，添加一个空格，然后将城镇放回：

SELECT A.Address,
       T.ID,
       T.Description,
       LEN(T.Description) AS TownLength,
       CONCAT(LEFT(A.Address, LEN(A.Address) - LEN(T.Description)), ' ', T.Description) AS NewAddress
FROM #Address AS A
     LEFT JOIN #Town AS T ON A.Address LIKE CONCAT('%', T.Description);

然后可以在如下的update语句中使用它，现在使用内部连接只更新与城镇名称匹配的地方：

UPDATE A
SET A.NewAddress = CONCAT(LEFT(A.Address, LEN(A.Address) - LEN(T.Description)), ' ', T.Description)
FROM #Address AS A
     INNER JOIN #Town AS T ON A.Address LIKE CONCAT('%', T.Description);

一种方法是使用LIKE连接表，并在REPLACE中使用townname：

SELECT  Address.Value
,       Town.Name
,       REPLACE(Address.Value, Town.Name, CONCAT(' ', Town.Name)) AddedSpace
FROM    (
            VALUES('UNIT 7 MID RISKBEITH ROADHOWWOODJOHNSTONE')
                , ('BELLWAY SITE HUTSCOLINTRAIVE CRESCENTBISHOPTON')
        ) AS Address(Value)
LEFT JOIN (
            VALUES('JOHNSTONE')
            ,       ('BISHOPTON')
            ) AS Town(Name)
        ON Address.Value LIKE CONCAT('%', Town.Name)

给出：

+------------------------------------------------+-----------+-------------------------------------------------+
|                     Value                      |   Name    |                   AddedSpace                    |
+------------------------------------------------+-----------+-------------------------------------------------+
| UNIT 7 MID RISKBEITH ROADHOWWOODJOHNSTONE      | JOHNSTONE | UNIT 7 MID RISKBEITH ROADHOWWOOD JOHNSTONE      |
| BELLWAY SITE HUTSCOLINTRAIVE CRESCENTBISHOPTON | BISHOPTON | BELLWAY SITE HUTSCOLINTRAIVE CRESCENT BISHOPTON |
+------------------------------------------------+-----------+-------------------------------------------------+

当然，这并不完全是万无一失的，例如，街道名称可能包含（或一个）城镇名称。