Geek asks and answers

How to update a row with the contents of this particular row + new data

I have approx. 60,000 lines with the street address in my db that contain a short version of the actual street address, for example.

Svarvarg. 11
Kungsg. 10
Stora g. 19

" g. " is an abbreviation of " gatan " and this creates problems in my application. So what I want to do is select all the lines containing " g. " And replace " g. " With " gatan " For example.

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

Choosing the whole street address that contains " g. " Is simple, but I can't figure out how to make a replacement in SQL. Could you help me with this.

+3
source share
3

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

. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

+1

- ?

update table
set ColumnName = replace(ColumnName, 'g.', 'gatan')
where ColumnName like '%g.%'
+2
UPDATE Foo SET Street = REPLACE(Street, 'g. ', 'gatan ')
0
source

More articles:


All Articles