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.com/images/img1.jpg

I want to update all the urls to another domain

 http://domain2.com/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 776 · Accepted Answer · answered Apr 16 '12 at 15:39

776

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

answered Apr 16 '12 at 15:39

Dmytro Shevchenko

29,307
6
48
64

Never thought this is possible! Thanks! – Ingus Oct 07 '20 at 06:08
1

Watch the CaSiNg (ask me how I know!). :) – HPWD May 10 '21 at 15:14
1

That saved hours, Thanks – Ronniee May 16 '21 at 05:52

score 185 · Answer 2 · answered Apr 16 '12 at 15:39

185

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

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

answered Apr 16 '12 at 15:39

Marc B

340,537
37
382
468

13

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

@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
4

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 29 · Answer 3 · answered Apr 16 '12 at 15:40

29

Try using the REPLACE function:

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

Note that it is case sensitive.

answered Apr 16 '12 at 15:40

schellack

9,654
1
27
32

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 9 · Answer 4 · edited Apr 22 '19 at 01:44

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 ('...//domain1.com/images/%');

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

Another example:

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

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

in any part of the string...

score 8 · Answer 5 · edited Jul 11 '18 at 02:18

8

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

40,722
30
82
105

answered Jul 11 '18 at 01:52

ManiMaran A

209
2
5

score 0 · Answer 6 · answered Sep 24 '19 at 06:27

0

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

answered Sep 24 '19 at 06:27

TechyFlick

79
5

Update a column value, replacing part of a string

6 Answers6

Linked

Related