CODEWITHSUNDEEP
sqlsql-server
James123
James123

Reputation: 11652

SQL Query: Update substring in column matching with another column value

I have a address column has values + in postal code. But + could be anywhere in the column (Street name, city) but I need to identity only postal code in the address with another column called Postal_Code and remove +.

It is going to be hard sql query to identify + in postal code and remove but don't know how to do?

I already remove + from Postal_Code column.

Table Example

POSTAL_CODE             FORMATTED_ADDRESS
T2E 8N6                 #6, 1435-40th Ave NE #6 Calgary AB  T2E+8N6 Canada
23401                   9 Abolaji Street Mafoluku +23401 Oshodi Lagos State Nigeria
234-01                  6 Force Road, Square Race Course +234-01 Lagos, Nigeria Lagos State
86 163453               Daqing Oilfield Co.Ltd Ranghulu 86+163453 Daqing CHN

Upvotes: 1

Views: 109

Answers (2)

LukStorms
LukStorms

Reputation: 29667

This seems hard in MS SQL Server.
(this would be so much easier with a regexp_replace function...)

Anyway....

Example snippet:

declare @YourTable table (POSTAL_CODE varchar(30), FORMATTED_ADDRESS varchar(1000));

insert into @YourTable (POSTAL_CODE, FORMATTED_ADDRESS) values
('T2E 8N6','#6, 1435-40th Ave NE #6 Calgary AB  T2E+8N6 Canada'),
('23401', '9 Abolaji Street Mafoluku +23401 Oshodi Lagos State Nigeria'),
('234-01', '6 Force Road, Square Race Course +234-01 Lagos, Nigeria Lagos State'),
('86 163453', 'Daqing Oilfield Co.Ltd Ranghulu 86+163453 Daqing CHN'),
(NULL, 'foo +1234 bar'),
('12345', 'bar 12345+ baz');

UPDATE @YourTable
SET FORMATTED_ADDRESS = 
    REPLACE(
       REPLACE(
          REPLACE(FORMATTED_ADDRESS, 
             '+'+POSTAL_CODE, POSTAL_CODE),
             POSTAL_CODE+'+', POSTAL_CODE),
             REPLACE(POSTAL_CODE,' ','+'), POSTAL_CODE)
WHERE POSTAL_CODE IS NOT NULL
AND (
     FORMATTED_ADDRESS LIKE CONCAT('%+', POSTAL_CODE, '%') OR
     FORMATTED_ADDRESS LIKE CONCAT('%', POSTAL_CODE, '+%') OR
     (POSTAL_CODE LIKE '%[ ]%' AND FORMATTED_ADDRESS LIKE CONCAT('%', REPLACE(POSTAL_CODE,' ','+'), '%'))
);


SELECT * FROM @YourTable;

Upvotes: 2

DavidG
DavidG

Reputation: 119017

This can be done with some REPLACE statements:

UPDATE YourTable
SET FORMATTED_ADDRESS = REPLACE(
        REPLACE(FORMATTED_ADDRESS, REPLACE(POSTAL_CODE, ' ', '+'), POSTAL_CODE), 
        '+'+POSTAL_CODE, 
        POSTAL_CODE)
FROM YourTable

You can test the results of this query by SELECTing first:

SELECT REPLACE(
        REPLACE(FORMATTED_ADDRESS, REPLACE(POSTAL_CODE, ' ', '+'), POSTAL_CODE), 
        '+'+POSTAL_CODE, 
        POSTAL_CODE) AS NEW_FORMATTED_ADDRESS,
        FORMATTED_ADDRESS
FROM YourTable

Upvotes: 0

Related Questions