MySQL MySQL查询用下划线替换列中的空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1806949/
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 query to replace spaces in a column with underscores
提问by Mithun Sreedharan
I have a MySQL database table 'photos' with a column 'filename'. I need to replace the spaces in the filename column values with underscores. Is it possible with a single/multiple query? If so how?
我有一个带有“文件名”列的 MySQL 数据库表“照片”。我需要用下划线替换文件名列值中的空格。是否可以使用单个/多个查询？如果是这样怎么办？
回答by Pascal MARTIN
You can use the REPLACEfunction :
您可以使用该REPLACE功能：
REPLACE(str,from_str,to_str)Returns the string
strwith all occurrences of the stringfrom_strreplaced by the stringto_str.REPLACE()performs a case-sensitive match when searching forfrom_str.
REPLACE(str,from_str,to_str)返回
str所有出现的字符串都from_str被字符串替换的字符串to_str。REPLACE()搜索时执行区分大小写的匹配from_str。
So, to replace all occurences of a character by another one in all lines of a table, something like this should do :
因此，要在表的所有行中用另一个字符替换所有出现的字符，应该执行以下操作：
update photos set filename = replace(filename, ' ', '_');
ie, you search for ' ' in the column filenameand use '_' instead ; and put the result back into filename.
即，您在列中搜索 ' 'filename并使用 '_' 代替；并将结果放回filename.
回答by Don
update photos set filename = replace(filename,' ', '_');
