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

Question

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

Are you looking update rows that are selected with something along the lines of: select * from my_table where FORMATTED_ADDRESS like '%+' + POSTAL_CODE + '%? — RToyo
– RToyo, Commented Jul 5, 2018 at 16:07
Do you want to UPDATE the FORMATTED_ADDRESS and remove those + if it's in front of the matching POSTAL_CODE? Or just SELECT them without the + ? — LukStorms
– LukStorms, Commented Jul 5, 2018 at 16:08

LukStorms · Accepted Answer · 2018-07-05 20:41:51Z

2

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;

edited Jul 5, 2018 at 20:41

answered Jul 5, 2018 at 16:44

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James123 Over a year ago

your code working perfectly good. only one condition it is not working when Mafoluku 23401+ Oshodi . Front + working but not rear + not

James123 Over a year ago

And also if your update state setting NULL if POSTAL_CODE is null

LukStorms Over a year ago

@James123 No longer selecting the empty postal_codes. And now also with + after it.

LukStorms Over a year ago

@James123 it's been simplified.

DavidG · Accepted Answer · 2018-07-05 16:43:49Z

0

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

edited Jul 5, 2018 at 16:43

answered Jul 5, 2018 at 16:17

DavidG

120k13 gold badges232 silver badges239 bronze badges

2 Comments

James123 Over a year ago

How to check this query works to see not breaking anything.??

DavidG Over a year ago

Check the edit, you can see what the UPDATE will do.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related