shuvrow shuvrow - 7 months ago 12
SQL Question

How to replace specific text at the beginning of a column in mysql?

I am trying to replace specific text from all the occurrence from a field in mysql.

I have following data in code field.

Code





hello12

12hello

12hel12

1212hi1





i want following output


hello

hel12

12hi1





I have tried following code

UPDATE code_table
SET code = REPLACE(code, '12', '')


Output


hello

hello

hel

hi1


I have also tried this code

UPDATE code_table
SET code = REPLACE(code, '12', '')
where code regexp ('^12')


But i am getting following output


hello

hel

hi1


This is expected because where clause only check whether data has 12 at the beginning of string and replace all occurrence of 12 from the string.

Is there any way to use regexp inside replace ?

Answer

You can select the record with the 12 prefix, then just replace the record with substring: update code_table set code = substring(code, locate("12", code)+length("12")) where code regexp '^12';