ahmed ahmed - 6 months ago 10
SQL Question

How to replace a substring to a column value in MySQL?

I have a big database in which the

website
column values are malformed:

http://.www.123.com.au, http://.www.exampleurl.com


I want to correct these urls by removing this extra character
.
as:

http://www.123.com.au, http://www.exampleurl.com


I get these values by passing following select statement:

SELECT *
FROM `tbl_business`
WHERE `website` LIKE 'http://.%';

Answer
UPDATE your_table
SET your_field = REPLACE(your_field, 'http://.www', ' http://www')
WHERE your_field LIKE '%http://.ww%';

The REPLACE function is the simplest way I know to do this and once you know it is there it is a very useful tool. In this case I guess you would not need the WHERE clause if it is a small number of records, but I would add it so you work on only subset of the data, better for larger data sets and to avoid unintended consequences.

Comments