MySQL string replace

开发者_C百科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?

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/

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

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.

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;

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.

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;