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.*$'.)
Source: Stackoverflow.com