MYSQL REGEXP query for keywords column

Question

i need a regular expression to get users have only keyword 52 and users who have keyword 52,53 but not 54. below is the table structure

CREATE TABLE `User` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `keywords` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sample record1:  100, Sam, Thompson, "50,51,52,53,54"
Sample record2:  100, Wan, Thompson, "50,52,53"
Sample record3:  100, Kan, Thompson, "53,52,50,54"

50 = sports
51 = cricket
52 = soccer
53 = baseball
54 = tennis

so far this is the query i have come up with. it gives records with all 3.

SELECT * FROM `User` WHERE keywords REGEXP '[[:<:]]52,53,54[[:>:]]'

Tim Biegeleisen · Accepted Answer · 2016-05-25 04:58:32Z

3

Try using FIND_IN_SET() rather than a complex regular expression:

SELECT u.*
FROM User
WHERE text = '52' OR
    (FIND_IN_SET('52', text) > 0 AND FIND_IN_SET('53', text) > 0 AND
     FIND_IN_SET('54', text) = 0)

Explanation:

WHERE text = '52' -- users who have keyword 52 and only this keyword
WHERE FIND_IN_SET('52', text) > 0 AND FIND_IN_SET('53', text) > 0 AND
     FIND_IN_SET('54', text) = 0
                  -- users who have keywords 52 and 53 but not 54

answered May 25, 2016 at 4:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Scott Weaver · Accepted Answer · 2016-05-25 21:19:52Z

1

SELECT * FROM `User` WHERE keywords RLIKE "^52$"; --only 52
SELECT * FROM `User` WHERE keywords RLIKE "(.*(52|53)){2}" AND keywords not RLIKE "54"; -- users who have keywords 52 and 53 but not 54

edit: for the record: the second query should really be not rlike [[:<:]]54[[:>:]], as in the OP's original idea, to prevent matching, say, 154.

http://sqlfiddle.com/#!9/cdc530/13

edited May 25, 2016 at 21:19

answered May 25, 2016 at 5:08

Scott Weaver

7,3832 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

MYSQL REGEXP query for keywords column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related