bonaca bonaca - 4 months ago 9
MySQL Question

find and replace values in a column

phpmyadmin

column name -

level


current values -
lev1
lev2
lev3
lev4
...

wanted values -
1
2
3
4
...

According to accepted and highly voted answer here, code should be like this:

UPDATE tmatria SET level = replace(level, 'lev', '');


But entering the above code inside phpmyadmin sql window I have a lot of errors.

What is the correct way to do this ?

Answer

If the value have alway the same prefix You can also use

UPDATE tmatria 
SET level = substr(level, 4, 100);