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.

Please always TAG your questions with the DB engine you are using. I can only guess it is MySQL. — Giannis Paraskevopoulos, Sep 13 '13 at 07:02
Use this link: http://dev.mysql.com/doc/refman/5.1/en/regexp.html — akozin, Sep 13 '13 at 07:12
`LIKE` does not support regex. The only wildcards supported are `%` and `_` (at least in standard SQL and MySQL) — , Sep 13 '13 at 07:57

Wietze314 · Accepted Answer · 2013-09-13T07:25:24.627

85

I think you can use REGEXP instead of LIKE

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

edited Sep 13 '13 at 07:25

answered Sep 13 '13 at 07:12

Wietze314

5,942
2
21
40

1

but it will search the records like `ALA101` and `BALA002`, and I only want `ALA101` to be searched – zzlalani Sep 13 '13 at 07:21
3

added the ^ in front of the regex. If you read in a bit about regular expressions you can do anything you want with it. Good luck! – Wietze314 Sep 13 '13 at 07:26
Thanks @Wietze314 i'm a little weak in regex, Thanks anyways – zzlalani Sep 13 '13 at 07:30
although it works but can you please guide me what is the use of `^` here? As far as I know it is being used as not to include something.. Where as the documentation says that it will `Match the beginning of a string.` http://dev.mysql.com/doc/refman/5.1/en/regexp.html .. I'm not sure how to elaborate this line of code `'^ALA[0-9]'` – zzlalani Sep 13 '13 at 08:19
2

Well, without `^` it will search every place in a string to find a pattern. Including it force to search string for pattern only from beginning - in other words string have to starts with pattern. – MKB Sep 13 '13 at 08:59
2

@zzlalani the `^` only means "not to include" when inside square brackets. Otherwise, it means "begins with". – modulitos Jul 18 '14 at 04:50
The parenthesis are there for good style, but not strictly required, right? – flow2k Feb 24 '20 at 01:38
the `` (quotes) are not required. The `[]` (parenthesis) are required, to make sure that a single digit after `ALA` matches the string. – Wietze314 Feb 25 '20 at 19:08

Andrew · Answer 2 · 2017-06-19T18:09:41.300

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

How to regex in a MySQL query

2 Answers2

Linked

Related