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

Question

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://.%';

Possible duplicate of [MySQL string replace](http://stackoverflow.com/questions/5956993/mysql-string-replace) — James Dewes, May 06 '16 at 17:09

James Dewes · Accepted Answer · 2016-06-02T21:05:26.807

2

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.

edited Jun 02 '16 at 21:05

answered May 06 '16 at 17:08

James Dewes

387
4
22

1

While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! – Michał Perłakowski May 06 '16 at 18:29

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

1 Answers1