How to regex in a MySQL query

Question

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.

Solution 1

I think you can use REGEXP instead of LIKE

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

Solution 2

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.*$'.)