Trying to extract TOWN from full address string, and place a leading space before TOWN

Imagine an ADDRESS table with 1 row other than PK, all the data has been scraped from a website using a tool, and TOWN is stuck to the previous part of the address, we have a lot of data gathered:

UNIT 7 MID RISKBEITH ROADHOWWOODJOHNSTONE 
BELLWAY SITE HUTSCOLINTRAIVE CRESCENTBISHOPTON

We need to convert this to (using 2 rows as example)...

UNIT 7 MID RISKBEITH ROADHOWWOOD JOHNSTONE 
BELLWAY SITE HUTSCOLINTRAIVE CRESCENT BISHOPTON

We have a table with a list of distinct valid TOWN_NAME which is 100% accurate and contains BISHOPTOWN and JOHNSTONE in table TOWN is TOWN_NAME, i.e.

table TOWN:

ID  DESCRIPTION
 1  JOHNSTONE
 2  BISHOPTOWN
 3  ABERDEEN
 4  GLASGOW

I've been trying to come up with a UPDATE STATEMENT that will process each ROW in ADDRESS table and insert a SPACE character before the TOWN NAME. TO do this, it can pattern match (I was thinking LIKE '%' _ TOWN_NAME so it matches then getting the no of characters in TOWN_NAME then inserting space. Trying to get the select working then can change to update

CodePudding user response：

Here's the code I used to recreate your example tables and a second column for NewAddress to be used later for comparison:

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

I then used this code which grabs the raw address and does a left join to the town list using a concat of '%' and town to match where the end of the address is exactly the same as one of the town names. I did some work to find the length of the town name, then used this to shorten the address, add a space, then put the town back on:

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

This can then be used in an update statement like this, now using an inner join to only update where there is a match on the town name:

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

CodePudding user response：

One way to do this is to join the tables with LIKE, and use the townname in a REPLACE:

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)

Gives:

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

Not exactly fool-proof, ofcourse, streetnames might contain the (or a) townname for example.