如何在mysql列中查找和替换一个单词?

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?

是否可以对这一列设置规则。这样，所有添加到此的数据都会自动格式化?或者我每次都需要重复这个查询?

2 个解决方案

#1

Run a query like this to update in the same column:

运行这样的查询，在同一列中更新:

UPDATE table 
   SET column = REPLACE(column, 'Street', 'St');

#2

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.

所以，如果我理解正确的话，你想要根据你是否在使用street这个词来修改数据库上的数据。我想这就是你需要的电话。

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?

这是你的意思吗?

#1