MySQL string replace

Question

I have a column containing urls (id, url):

http://www.example.com/articles/updates/43
http://www.example.com/articles/updates/866
http://www.example.com/articles/updates/323
http://www.example.com/articles/updates/seo-url
http://www.example.com/articles/updates/4?something=test

I'd like to change the word "updates" to "news". Is it possible to do this with a script?

Possible duplicate of [MySql - Way to update portion of a string?](http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string) — Steve Chambers, Aug 02 '16 at 14:03

score 1348 · Accepted Answer · edited Dec 15 '17 at 10:47

1348

UPDATE your_table
SET your_field = REPLACE(your_field, 'articles/updates/', 'articles/news/')
WHERE your_field LIKE '%articles/updates/%'

Now rows that were like

http://www.example.com/articles/updates/43

will be

http://www.example.com/articles/news/43

http://www.electrictoolbox.com/mysql-find-replace-text/

edited Dec 15 '17 at 10:47

rogerdpack

50,731
31
212
332

answered Jan 29 '13 at 15:31

Giraldi

14,421
4
28
48

24

Quick question, is it really needed to have the "WHERE" clause? – John Crawford Jul 17 '13 at 08:08
59

@JohnCrawford According to the article in the link: "You don't necessarily have to add the `WHERE LIKE` clause at the end, because if the text to find isn't there the row won't be updated, **but it should speed things up**." – Giraldi Jul 17 '13 at 12:02
4

WHERE clause gives you specific control over what gets replaced. Without one every row will be checked and potentially data replaced if a match is found. – Carlton Sep 11 '14 at 10:53
11

I believe in this case the WHERE is useless because a `LIKE '%%'` does not use any indexes, if there were other parts in that WHERE, for example something like `date_added > '2014-07-01'` it might have helped – Fabrizio Sep 23 '14 at 22:35
13

I always come here for reference when I need to replace something in mysql – Daniel Pecher Apr 13 '15 at 08:57
3

The WHERE is useful for this reason: Only the rows that need to be written to will be written to. Without the WHERE, all rows would be written to, even though the values in some rows would not be changing. If your table is large, or if you have triggers, the WHERE will avoid a lot of unnecessary processing. – Liam Sep 25 '15 at 18:04
1

@Liam "all rows would be written to, even though the values in some rows would not be changing" what is written if nothing changes? Can you provide a source for that? Given the replace pattern and where clause are identical, it will read every row but it will not _write_ to every row, only those that match the pattern. If the where clause were something other than the exact same replace pattern, then it could be important. It's good practice to write a where clause first, and use transaction rollback - prevent yourself from accidentally updating an entire table, but here it's redundant. – Davos Jul 03 '17 at 13:22
I came across this and wondered. So I tested it out in a my database. Looks like using a redundant LIKE statement is slowing things down. Makes sense to me, the REPLACE function surely is optimized for this use case while the additional LIKE statement just adds no benefit. – Thomas Rückert Aug 05 '20 at 06:26

score 145 · Answer 2 · answered May 10 '11 at 22:01

145

Yes, MySQL has a REPLACE() function:

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

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

Note that it's easier if you make that an alias when using SELECT

SELECT REPLACE(string_column, 'search', 'replace') as url....

answered May 10 '11 at 22:01

onteria_

60,174
6
66
62

As long as the OP's `updates` only shows up once in the string, then this would work. Otherwise you're stuck with direct string manipulation which is a real pain in MySQL. At that point it'd be easier to write a one-off script to select the fields, manipulation in the client, then write back. – Marc B May 10 '11 at 22:07

score 21 · Answer 3 · edited Jun 22 '17 at 14:38

21

The replace function should work for you.

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

edited Jun 22 '17 at 14:38

Nicholas Shanks

9,417
3
51
73

answered May 10 '11 at 22:01

Jay

12,504
3
36
65

score 12 · Answer 4 · answered Jan 30 '18 at 11:41

You can simply use replace() function,

with where clause-

update tabelName set columnName=REPLACE(columnName,'from','to') where condition;

without where clause-

update tabelName set columnName=REPLACE(columnName,'from','to');

Note: The above query if for update records directly in table, if you want on select query and the data should not be affected in table then can use the following query-

select REPLACE(columnName,'from','to') as updateRecord;

score 6 · Answer 5 · answered Oct 13 '13 at 16:09

In addition to gmaggio's answer if you need to dynamically REPLACE and UPDATE according to another column you can do for example:

UPDATE your_table t1
INNER JOIN other_table t2
ON t1.field_id = t2.field_id
SET t1.your_field = IF(LOCATE('articles/updates/', t1.your_field) > 0, 
REPLACE(t1.your_field, 'articles/updates/', t2.new_folder), t1.your_field) 
WHERE...

In my example the string articles/news/ is stored in other_table t2 and there is no need to use LIKE in the WHERE clause.

MySQL string replace

5 Answers5

Linked

Related