Hi there,
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?
+1
A:
UPDATE action_4_members
SET mobile = CONCAT('64', SUBSTRING(mobile,2))
WHERE SUBSTRING(mobile,1,1) = '0'
Kdeveloper
2010-10-13 03:29:25
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)'
Hailwood
2010-10-13 03:32:24
In an UPDATE statement you don't need the IF this should be done in the WHERE clause. I adjusted the code.
Kdeveloper
2010-10-13 03:34:43
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
2010-10-13 03:38:48
ah fixed it, had to use 0 instead of '0'
Hailwood
2010-10-13 03:41:00
Damn, Actually that just updated all rows...
Hailwood
2010-10-13 03:47:22