CODEWITHSUNDEEP
mysqlsqldatabase
user398843
user398843

Reputation: 191

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

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.

Upvotes: 1

Views: 1686

Answers (1)

Jonas Metzler
Jonas Metzler

Reputation: 5975

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.

Upvotes: 2

Related Questions