Update a column value, replacing part of a string

Question

I have a table with the following columns in a MySQL database

[id, url]

And the URLs are like:

 http://domain1.example/images/img1.jpg

I want to update all the URLs to another domain

 http://domain2.example/otherfolder/img1.jpg

keeping the name of the file as is.

What's the query must I run?

Possible duplicate of [MySQL string replace](http://stackoverflow.com/questions/5956993/mysql-string-replace) — Steve Chambers, Aug 02 '16 at 13:59

score 919 · Accepted Answer · edited Jun 25 '22 at 11:04

919

UPDATE urls
SET url = REPLACE(url, 'domain1.example/images/', 'domain2.example/otherfolder/')

edited Jun 25 '22 at 11:04

Stephen Ostermiller

23,933
14
88
109

answered Apr 16 '12 at 15:39

Dmytro Shevchenko

33,431
6
51
67

2

Watch the CaSiNg (ask me how I know!). :) – HPWD May 10 '21 at 15:14
It's so awesome when MySQL can do such stuff out of the box so you don't need to manage it via server-side languages) – Alex Jul 26 '21 at 09:12

score 222 · Answer 2 · edited Jun 25 '22 at 11:04

222

UPDATE yourtable
SET url = REPLACE(url, 'http://domain1.example/images/', 'http://domain2.example/otherfolder/')
WHERE url LIKE ('http://domain1.example/images/%');

relevant docs: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace

edited Jun 25 '22 at 11:04

Stephen Ostermiller

23,933
14
88
109

answered Apr 16 '12 at 15:39

Marc B

356,200
43
426
500

13

Hi there- why do I need the where ? – Guy Cohen Oct 07 '14 at 14:21
22

@GuyCohen Because otherwise the query will modify every single row in the table. The `WHERE` clause optimizes the query to only modify the rows with certain URL. Logically, the result will be the same, but the addition of `WHERE` will make the operation faster. – Dmytro Shevchenko Aug 14 '15 at 14:34
6

The `WHERE` also ensures that you're only replacing parts of strings that _begin_ with `http://etc/etc/` or `string_to_be_replaced.` For example, in the given answer, `http://domain1.com/images/this/is/a/test` would be affected, but `foobar/http://domain1.com/images/` would not. – Kyle Challis Jan 29 '16 at 19:03

score 30 · Answer 3 · edited Jun 25 '22 at 11:05

30

Try using the REPLACE function:

mysql> SELECT REPLACE('www.example.com', 'w', 'Ww');
        -> 'WwWwWw.example.com'

Note that it is case sensitive.

edited Jun 25 '22 at 11:05

Stephen Ostermiller

23,933
14
88
109

answered Apr 16 '12 at 15:40

schellack

10,144
1
29
33

2

@UniversalGrasp See a lot of possible answers here : http://stackoverflow.com/questions/5656056/case-insensitive-replace-in-mysql – Jonathan Bergeron Mar 09 '16 at 15:05

score 15 · Answer 4 · edited Jul 11 '18 at 02:18

15

Try this...

update [table_name] set [field_name] = 
replace([field_name],'[string_to_find]','[string_to_replace]');

edited Jul 11 '18 at 02:18

Stephen Rauch

47,830
31
106
135

answered Jul 11 '18 at 01:52

ManiMaran A

309
3
6

Exact answer, What am looking for. – Abijith Ajayan Jul 03 '23 at 09:14

score 10 · Answer 5 · edited Jun 25 '22 at 11:05

You need the WHERE clause to replace ONLY the records that complies with the condition in the WHERE clause (as opposed to all records). You use % sign to indicate partial string: I.E.

LIKE ('...//example.com/images/%');

means all records that BEGIN with "...//example.com/images/" and have anything AFTER (that's the % for...)

Another example:

LIKE ('%http://example.com/images/%')

which means all records that contains "http://example.com/images/"

in any part of the string...

score 4 · Answer 6 · edited Apr 20 '22 at 03:34

4

First, have to check

SELECT * FROM `university` WHERE course_name LIKE '%&amp%'

Next, have to update

UPDATE university
SET course_name = REPLACE(course_name, '&amp', '&') WHERE id = 1

Results: Engineering &amp Technology => Engineering & Technology

edited Apr 20 '22 at 03:34

Peyman Mohamadpour

17,954
24
89
100

answered Sep 24 '19 at 06:27

Solomon Suraj

1,162
8
8

Update a column value, replacing part of a string

6 Answers6

Linked

Related