CODEWITHSUNDEEP
mysqlsqlregex
Darren
Darren

Reputation: 3059

Regex for MySQL query

I am trying to select a field based on it meeting one of 3 criteria... and I'm not sure how to do this. I think a RegExp is probably the best method buy I'm unfamiliar with writing them.

Say I have the integer 123, I would like to match the following cases:

123 (thats 123 only with no spaces or other numbers after it) 123-10/12/2007 00:00 (thats 123 with a hyphen and a date, or actually it could be anything after the hyphen) 123_1014859 (thats 123 with an underscore, or again anything after the underscore)

Is there a way to do this using MySQL?

Upvotes: 1

Views: 540

Answers (3)

eagle12
eagle12

Reputation: 1668

I would avoid using regex inside a SQL statement. Someone can correct me if I am wrong, but MySQL has to use another engine to run the regex.

SELECT * FROM table 
  WHERE field like "123" 
    OR field LIKE "123-%" 
    OR field like "123_%";

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332521

A regex is plausible, but it's not the best performing option. The last comparison put MySQL's regex support as being par with wildcarding the left side of a LIKE statement -- works, but the slowest of every option available.

Based on your example, you could use:

SELECT t.*
  FROM YOUR_TABLE t
 WHERE t.column LIKE '123-%'
    OR t.column LIKE '123_%'

Another alternative, because OR can be a performance issue too, would be to use a UNION:

SELECT a.*
  FROM YOUR_TABLE a
 WHERE a.column LIKE '123-%'
UNION ALL
SELECT b.*
  FROM YOUR_TABLE b
 WHERE b.column LIKE '123_%'

UNION ALL will return all results from both tables; UNION removes duplicates, and is slower than UNION ALL for that fact.

Upvotes: 2

Matt Ball
Matt Ball

Reputation: 359776

select * from foo where bar regexp '^123-|_'

(not tested)

Upvotes: 1

Related Questions