How to select data where the last three characters are numbers?-CodePudding

I have a table with a column having values like:

AB123
AB209
ABQ52
AB18C

I would like to extract rows whose last three characters are numbers. How can I do this?

The original table is more complicated, and I tried the "WHERE" clause with "AB___", which returned the above to me.

CodePudding user response：

You can use a combination of SUBSTRING and REGEXP like this:

SELECT yourcolumn FROM yourtable WHERE SUBSTRING(yourcolumn, -3) REGEXP '^[0-9] $';

The SUBSTRING part will cut the last 3 characters of the column's value and the REGEXP condition will check whether this substring is numeric.