MySQL REGEXP for numbers that begin with ..

Question

Hi guys.

I need to write a query using MYSQL REGEXP that will find me rows that have a certain column begin with 11 or 12 or etc. I know I can use LIKE or LEFT(####,2) but would like to use the REGEXP option. My data is stored as 110001, 122122, 130013a and etc.

EDIT 1:

To make it more clear, I would like to express

SELECT * FROM table WHERE column LIKE '11%' or column LIKE '12%' or column LIKE '30%'"     

with REGEXP

Thanks!

Answer 1

To match rows that start with any two digits you can use:

SELECT *
FROM yourtable
WHERE yourcolumn REGEXP '^[0-9][0-9]';

If you only want to allow rows starting with 11, 12 or 30 then you could use this:

SELECT *
FROM yourtable
WHERE yourcolumn REGEXP '^(11|12|30)';

This will not be able to use an index on the column so it may be slower than using LIKE.