Regex for MySQL query

Question

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?

OMG Ponies · Accepted Answer · 2011-06-07 02:35:44Z

2

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.

answered Jun 7, 2011 at 2:35

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Ball · Accepted Answer · 2011-06-07 02:34:27Z

1

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

(not tested)

answered Jun 7, 2011 at 2:34

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

Comments

eagle12 · Accepted Answer · 2011-06-07 02:36:25Z

1

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_%";

answered Jun 7, 2011 at 2:36

eagle12

1,67811 silver badges14 bronze badges

1 Comment

OMG Ponies Over a year ago

I don't know that MySQL uses another engine -- SQL Server 2005+ requires CLR (which offloads to .NET) if you want regex support...

Collectives™ on Stack Overflow

Regex for MySQL query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related