CODEWITHSUNDEEP
mysqlregex
user2977468
user2977468

Reputation: 183

Wrong pattern when using regexp w/ mysql ..?

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 !!

Upvotes: 1

Views: 80

Answers (1)

Animism
Animism

Reputation: 496

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.

Upvotes: 4

Related Questions