CODEWITHSUNDEEP
mysqlsql
Addev
Addev

Reputation: 32263

Update a column value, replacing part of a string

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?

Upvotes: 460

Views: 589665

Answers (6)

Kenneth Daly
Kenneth Daly

Reputation: 101

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...

Upvotes: 10

schellack
schellack

Reputation: 10274

Try using the REPLACE function:

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

Note that it is case sensitive.

Upvotes: 32

Marc B
Marc B

Reputation: 360872

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

Upvotes: 240

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34641

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

Upvotes: 954

Solomon Suraj
Solomon Suraj

Reputation: 1324

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

Upvotes: 6

ManiMaran A
ManiMaran A

Reputation: 359

Try this...

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

Upvotes: 19

Related Questions