CODEWITHSUNDEEP
mysqlsqlsql-update
jakob
jakob

Reputation: 6005

How to update row with the contents of that specific row + new data

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

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.

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.

Upvotes: 3

Views: 78

Answers (3)

Alex Aza
Alex Aza

Reputation: 78487

Something like this?

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

Upvotes: 2

Lasse Edsvik
Lasse Edsvik

Reputation: 9298

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

Upvotes: 0

jishi
jishi

Reputation: 24634

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

Upvotes: 1

Related Questions