CODEWITHSUNDEEP
mysql
jmenezes
jmenezes

Reputation: 1926

mySql replace only a single word in a column

Using mySql alone, I'm trying to replace the word microsoft with microsoft-corp.

I tried using this sql, update names set names = replace(names,'microsoft','microsoft-corp'), but that replaces each and every microsoft. Is there a way to get it to replace only the word microsoft?

Original data

"id"    "names"
"1"     "/microsoft"
"2"     "/microsoft/microsoft-word"
"3"     "/microsoft/microsoft-word/microsoft-word-tutorials"
"4"     "/microsoft/microsoft-word/microsoft-word-tutorials/examples"

"id"    "names"
"1"     "microsoft >"
"2"     "microsoft > microsoft-word"
"3"     "microsoft > microsoft-word > microsoft-word-tutorials"
"4"     "microsoft > microsoft-word > microsoft-word-tutorials > examples"

Expected correct results

"id"    "names"
"1"     "/microsoft-corp"
"2"     "/microsoft-corp/microsoft-word"
"3"     "/microsoft-corp/microsoft-word/microsoft-word-tutorials"
"4"     "/microsoft-corp/microsoft-word/microsoft-word-tutorials/examples"

Current incorrect results using my query above

"id"    "names"
"1"     "/microsoft-corp"
"2"     "/microsoft-corp/microsoft-corp-word"
"3"     "/microsoft-corp/microsoft-corp-word/microsoft-corp-word-tutorials"
"4"     "/microsoft-corp/microsoft-corp-word/microsoft-corp-word-tutorials/examples"

Upvotes: 1

Views: 162

Answers (2)

Kev
Kev

Reputation: 119806

How about:

update names set names = replace(names,'/microsoft/','/microsoft-corp/')

update names set names = '/microsoft-corp' where names = '/microsoft'

update names set names = replace(names,'microsoft >','microsoft-corp >')

Upvotes: 2

Brian Hoover
Brian Hoover

Reputation: 7991

I would probably do this in two passes, although you might be able to it in one pass.

update names set names = replace(names,'/microsoft/','/microsoft-corp/');
update names set names = '/microsoft-corp' where names = '/microsoft';

Upvotes: 0

Related Questions