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?

回答1:

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/

回答2:

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

回答3:

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.

回答4:

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.

回答5:

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;