如何用特定行的内容和新数据更新行

I have approx. 60000 rows with street address in my db that contain short version of the actual street address eg.

我有大约。我的数据库中有60000行街道地址，其中包含实际街道地址的简短版本。

Svarvarg. 11
Kungsg. 10
Stora g. 19

"g." is a abbreviation of "gatan" and this creates problems within my application. So what I want do to is to Select all rows that contain "g." and replace "g." with "gatan" Eg.

“g.”是“gatan”的缩写，这在我的应用程序中造成了一些问题。所以我要做的是选择包含“g.”的所有行，并将“g.”替换为“gatan”Eg。

Svarvarg. 11 -> Svarvargatan 11
Kungsg. 10 -> Kungsgatan 10
Stora g. 19 -> Stora gatan 19

The selection of all street address that contain "g." is simple but I can't figure out how to do the replacement in SQL. Could you please help me with that.

所有包含“g.”的街道地址的选择都很简单，但是我不知道如何用SQL替换。你能帮我一下吗?

3 个解决方案

#1

just use

只使用

UPDATE table SET column = REPLACE(column, 'g.', 'gatan') WHERE ...

See this documentation 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看到这个文档

#2

Something like this?

是这样的吗?

update table
set ColumnName = replace(ColumnName, 'g.', 'gatan')
where ColumnName like '%g.%'

#3

UPDATE Foo SET Street = REPLACE(Street, 'g. ', 'gatan ')

#1