Wrong pattern when using regexp w/ mysql ..?

Question

I have a mysql table that's pretty simple & small.

award_id bigint(11) primary key auto_inc  
award_receip varchar(160) NULL  
date_receip DATETIME NOT NULL

When I make the following query, I don't get the expected results.

SELECT * FROM awards WHERE award_id REGEXP '("1|6|3")'

Only award_id 6 is in the result set. The primary keys are all consecutive into the thousands.

What's the error in my pattern that prevents award id 1 & award id 3 from being displayed in the results ?

Thanks !!

Animism · Accepted Answer · 2013-12-03 04:49:18Z

4

You shouldn't be using a regex at all. award_id is BIGINT, so use IN:

SELECT * FROM awards WHERE award_id IN (1,6,3);

Just for completeness, if one were sadistic enough to use a regex, two of many possible patterns would be:

SELECT * FROM awards WHERE award_id RLIKE '^(1|6|3)$';
SELECT * FROM awards WHERE (award_id LIKE '1' OR award_id LIKE '6' OR award_id LIKE '3');

But yeah, don't use those, just use IN.

answered Dec 3, 2013 at 4:44

Animism

4964 silver badges10 bronze badges

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

1 Comment

much faster method i assume ... great solution. Thank you.