How to regex in a MySQL query

mysql sql regex innodb

zzlalani · Sep 13, 2013 · Viewed 200.6k times · Source

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this

SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%')

And

SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[0-9]%')

But both of the queries always return a null record

trecord
-------
null

Where as if I execute the following query

SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA%')

it returns

trecord
-------
ALA0000
ALA0001
ALA0002

It means that I have records that starts with ALA and a digit after it,

EDIT

I'm doing it using PHP MySQL and innodb engine to be specific.

Answer

I think you can use REGEXP instead of LIKE

SELECT trecord FROM `tbl` WHERE (trecord REGEXP '^ALA[0-9]')