How to regex in a MySQL query

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.

I think you can use REGEXP instead of LIKE

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

In my case (Oracle), it's WHERE REGEXP_LIKE(column, 'regex.*'). See here:

> SQL Function > > Description > > > ---------- > > REGEXP_LIKE > > This function searches a character column for a pattern. Use this > function in the WHERE clause of a query to return rows matching the > regular expression you specify. > > ... > > REGEXP_REPLACE > > This function searches for a pattern in a character column and > replaces each occurrence of that pattern with the pattern you specify. > > ... > > REGEXP_INSTR > > This function searches a string for a given occurrence of a regular > expression pattern. You specify which occurrence you want to find and > the start position to search from. This function returns an integer > indicating the position in the string where the match is found. > > ... > > REGEXP_SUBSTR > > This function returns the actual substring matching the regular > expression pattern you specify.

(Of course, REGEXP_LIKE only matches queries containing the search string, so if you want a complete match, you'll have to use '^$' for a beginning (^) and end ($) match, e.g.: '^regex.*$'.)