SQL update to remove postcode from address (where postcode is already in a separate column)-CodePudding

I have an issue where the postcode was included in an address field for hundreds of records. The postcode is already provided in a separate column.

How to I perform a SQL update to remove just the postcode from the end of the address field in all records that contain the contents of the post_code field at the end address field?

Example:

address                            | post_code
1 Street View, Somewhere, 1AA AA1  | 1AA AA1
2 Road Place, Some City, 2AA AA2   | 2AA AA2

After SQL update:

address                            | post_code
1 Street View, Somewhere           | 1AA AA1
2 Road Place, Some City            | 2AA AA2

Thanks

CodePudding user response：

You could use the following update query:

UPDATE yourTable
SET address = REPLACE(address, ', '   post_code, '')
WHERE address LIKE '%, '   post_code;

If the trailing post code be not comma separated, then use this version:

UPDATE yourTable
SET address = REPLACE(address, ' '   post_code, '')
WHERE address LIKE '% '   post_code;