mysql sub_string replace?

Question

Is it possible in mysql to replace the first character of a cell with something else?

eg I have

64123
64340
64067
0104
0240
0456

I need to replace all the starting 0's with 64

how could I do this?

Do you mean replace the first 0 only if it is the first char? — alex
– alex, Commented Oct 13, 2010 at 3:27
That would be correct, I know I can use Replace() to replace ALL zero's but how do I do only the first zero? — Hailwood
– Hailwood, Commented Oct 13, 2010 at 3:28

Kdeveloper · Accepted Answer · 2010-10-13 03:42:47Z

1

UPDATE action_4_members
SET mobile = CONCAT('64', SUBSTRING(mobile,2))
WHERE SUBSTRING(mobile,1,1) = '0'

edited Oct 13, 2010 at 3:42

answered Oct 13, 2010 at 3:29

Kdeveloper

13.8k11 gold badges44 silver badges50 bronze badges

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

4 Comments

Hailwood Over a year ago

I have attempted

UPDATE action_4_members SET mobile = (IF SUBSTRING(mobile,0,1) == "0" THEN CONCAT("64", SUBSTRING(mobile,1)) ELSE mobile);

but I get syntax error near 'SUBSTRING(mobile,0,1) == "0" THEN CONCAT("64", SUBSTRING(mobile,1)) ELSE mobile)'

Kdeveloper Over a year ago

In an UPDATE statement you don't need the IF this should be done in the WHERE clause. I adjusted the code.

Hailwood Over a year ago

My bad, Now I have tried UPDATE action_4_members SET mobile` = CONCAT("64", SUBSTRING(mobile,1)) WHERE SUBSTRING(mobile,0,1) = '0';` But its not updating any rows.

Hailwood Over a year ago

ah fixed it, had to use 0 instead of '0'

Collectives™ on Stack Overflow

mysql sub_string replace?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related