CODEWITHSUNDEEP
mysqlreplace
n00b
n00b

Reputation: 16556

MySQL string replace

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?

Upvotes: 665

Views: 859163

Answers (6)

Ganesh Giri
Ganesh Giri

Reputation: 1161

The REPLACE function is very handy to search and replace text in a table such as updating obsolete URL, correcting a spelling mistake, etc.

  UPDATE tbl_name 
    SET 
        field_name = REPLACE(field_name,
            string_to_find,
            string_to_replace)
    WHERE
        conditions;

Upvotes: 5

Deepak Kumbhar
Deepak Kumbhar

Reputation: 544

You can simply use replace() function.

Example:

with where clause-

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

without where clause-

update tableName 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;

Upvotes: 20

Giraldi
Giraldi

Reputation: 17321

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/

Upvotes: 1488

Jay
Jay

Reputation: 14471

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.

Upvotes: 25

RafaSashi
RafaSashi

Reputation: 17215

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.

Upvotes: 7

onteria_
onteria_

Reputation: 70577

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

Upvotes: 152

Related Questions