MySQL : How to remove string from column

Very simple I ha开发者_运维百科ve a column of string like that :

 abc1
 abc2
 abc3
 abc4
 abc5

I need to remove 'abc' from this column and keep only the number on the right, so column would have only the number like :

 1
 2
 3
 4
 5

I thought about smth like that but it doesn't work

update table2
-> set col1 = case when 'abc1' then 1
-> else end;

I know how to concat text, I don't know how to undo it... Help please ^^;

---

@McArthey already hinted at it, but this is easy to do when the "abc" is consistently "abc" (i.e. the length doesn't change.)

Amongst the various string functions is one in particular: RIGHT(). This allows you to select a fixed number of characters from a string. e.g.

SELECT RIGHT('abc3',1) -- Results in "3"
SELECT RIGHT('abc3',2) -- Results in "c3"

Coupled with the LENGTH() function, you can conclude the numbers are anything past the 3rd character. i.e.

SELECT RIGHT('abc3',LENGTH('abc3')-3) -- Results in "3"

Obviously I'm using hard strings ('abc3'), but these can easily be replaced with column names.

The caveat here is that these all are based on fixed length letter prefixes. The more variable (changing) the "abc" in your example is, the harder picking the numeric value out of the column becomes.

---

If these are single digit values you could use

select right(column,1) ...

You may also find the REGEXP docs useful if it is more complex.

If you are trying to modify the column you will have to take the values separately and then concatenate them back together. It's difficult to give a precise answer since I don't know what you're trying to accomplish but you could do something with SUBSTR to grab the separate values.

Get 'abc': SUBSTR(column, 1,3)

Get digits: SUBSTR(column, 4)