MySQL MySQL字符串替换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5956993/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL string replace
提问by n00b
I have a column containing urls (id, url):
我有一列包含 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?
我想把“更新”这个词改成“新闻”。可以用脚本来做到这一点吗？
回答by Giraldi
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
http://www.example.com/articles/updates/43
will be
将会
http://www.example.com/articles/news/43
http://www.example.com/articles/news/43
回答by onteria_
Yes, MySQL has a REPLACE() function:
是的，MySQL 有一个 REPLACE() 函数：
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
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
SELECT REPLACE(string_column, 'search', 'replace') as url....
回答by Jay
The replacefunction 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.
返回字符串 str，其中所有出现的字符串 from_str 替换为字符串 to_str。REPLACE()搜索 from_str 时执行区分大小写的匹配。
回答by Deepak Kumbhar
You can simply use replace() function,
您可以简单地使用 replace() 函数，
with where clause-
使用 where 子句-
update tabelName set columnName=REPLACE(columnName,'from','to') where condition;
without where clause-
没有 where 子句-
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;
回答by RafaSashi
In addition to gmaggio's answerif you need to dynamically REPLACEand UPDATEaccording to another column you can do for example:
除了 gmaggio 的答案之外，如果您需要动态REPLACE并UPDATE根据另一列，您可以执行以下操作，例如：
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 t2and there is no need to use LIKEin the WHEREclause.
在我的示例中，字符串articles/news/存储在子句中，other_table t2无需LIKE在WHERE子句中使用。
