ahmed ahmed - 1 year ago 46
SQL Question

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

I have a big database in which the

column values are malformed:

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

I want to correct these urls by removing this extra character

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

I get these values by passing following select statement:

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

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.