MySQL 如何在mysql列中查找和替换单词？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6171512/
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
how to find and replace a word in a mysql column?
提问by Shyam Natraj Kanagasabapathy
I have a column containing list of streets. I need to replace 'street' with 'St'. The replacement can be made in the current column or in a new column with the address in the required format. The following is the sample data. 'Column 1' contains the data in current format. 'Column 2' contains the data in the desired format.
我有一列包含街道列表。我需要用'St'替换'street'。可以在当前列或具有所需格式的地址的新列中进行替换。以下是样本数据。“第 1 列”包含当前格式的数据。“第 2 列”包含所需格式的数据。
Column 1 Column 2
Hillary Street Hillary St
Golf Road Golf Road
Oldwood Street Oldwood St
How do I do this?
我该怎么做呢？
Edit:
编辑：
This query works for this:
此查询适用于：
UPDATE table SET column = REPLACE(column, 'Street', 'St');
Is it possible to set a rule to this column. Such that all data added to this get formatted this way automatically? Or I need to repeat this query each time?
是否可以为此列设置规则。这样所有添加到此的数据都会自动以这种方式格式化？或者我每次都需要重复这个查询？
回答by AJ.
Run a query like this to update in the samecolumn:
运行这样的查询以在同一列中更新：
UPDATE table
SET column = REPLACE(column, 'Street', 'St');
回答by Andres C
So, if i understand correctly, you want to modify the data on the database based on wether or not you're using the word street. I think this is the call u need.
因此，如果我理解正确，您想根据您是否使用“街道”一词来修改数据库中的数据。我认为这是你需要的电话。
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
I think this is the method you need to use, so your ending code will look something like
我认为这是您需要使用的方法，因此您的结束代码将类似于
update myTable set address = replace(address,'street','St');
Is this what you meant?
这是你的意思吗？
