MySQL replace function not specific enough

Question

 UPDATE `link_tag`
 SET `TagID` = replace(TagID, 2, 13)

I'm specifically only trying to replace 2 with 13. When I use the above for example 202 becomes 13013 because it is replacing both instances of 2 within the number 202. I only want to replace just 2 with 13, not 2 within other larger numbers.

MySQL has no built in regex replacement support, and this could be very tricky. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 19, 2017 at 6:29

Florian Humblot · Accepted Answer · 2017-05-19 06:32:04Z

1

If you specify a where clause, you'll only update the results that match the exact clause so you could try something like this:

UPDATE `link_tag`
SET `TagID` = replace(`TagID`, 2, 13)
WHERE `TagID` = 2

Now it'll only change the 2 where the TagID is actually equal to 2

A better way would be doing this:

UPDATE `link_tag` SET `TagID` = 13 WHERE `TagID` = 2

edited May 19, 2017 at 6:32

answered May 19, 2017 at 6:30

Florian Humblot

95511 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Florian Humblot Over a year ago

@TimBiegeleisen Could you elaborate as to why not ?

Florian Humblot Over a year ago

@TimBiegeleisen That's the problem OP has though, he only wants to change a single "2" if that's the exact value of the column, he doesn't want a column "22" to become "1313"

Florian Humblot Over a year ago

@TimBiegeleisen It's not a problem, but I address the last sentence of the OP's post, where he explicitly states that this is his intention :)

Collectives™ on Stack Overflow

MySQL replace function not specific enough

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related