mysql where string ends with numbers

Question

My table column contains values like:

id | item
-------------
1  | aaaa11a112
2  | aa1112aa2a
3  | aa11aa1a11
4  | aaa2a222aa

I want to select only rows where value of item ends with numbers. Is there something like this?

select * from table where item like '%number'

Answer 1

You can use REGEXP and character class

select * from table where item REGEXP '[[:digit:]]$'

DEMO

Explanation:

[[:digit:]] >> Match digit characters
$           >> Match at the end of the string

Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class.

---

SIDENOTE:

Other helpful mysql character classes to use with REGEXP, taken from the documentation:

Character Class Name    Meaning
alnum                   Alphanumeric characters
alpha                   Alphabetic characters
blank                   Whitespace characters
cntrl                   Control characters
digit                   Digit characters
graph                   Graphic characters
lower                   Lowercase alphabetic characters
print                   Graphic or space characters
punct                   Punctuation characters
space                   Space, tab, newline, and carriage return
upper                   Uppercase alphabetic characters
xdigit                  Hexadecimal digit characters

Answer 2

you can use REGEXP

select * from table where  RIGHT(item ,1) REGEXP '^-?[0-9]+$';