mySQL REGEXP match value between commas

Question

I've been looking into the REGEXP when filtering my entries in my database. I have a columns with values separated by commas looking like:
id        col A
|---|------------------------|
| 1 | P:1,P:2,P:5,P:7     |
| 2 | P:6,P:8,P:10,P:11 |
| 3 | P:4,P:3,P1,P:0      |
| 4 | P:2,P:1                |
|---|------------------------|

Let's say I want the rows containing the value P:1, how can i design a REGEXP in the form:

SELECT * FROM `table` WHERE `col A` REGEXP '?'

so that i get rows 1 3 and 4? My previous approach was simply to use:

SELECT * FROM `table` WHERE `col A` LIKE 'P:1'

However that would naturally also return row 2 because it technically contains P:1...
Any help would be appreciated, I thinking this problem is fairly simple for a regexp expert!

Cheers,
Andreas

Madbreaks · Accepted Answer · 2012-11-13 22:53:40Z

1

You need to read up on word boundaries.

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

answered Nov 13, 2012 at 22:53

Madbreaks

19.6k7 gold badges62 silver badges75 bronze badges

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

1 Comment

Andreas Jarbol Over a year ago

Yep that is the answer: SELECT * FROM table WHERE col A REGEXP '[[:<:]]P:1[[:>:]]'; Thanks a lot!

Collectives™ on Stack Overflow

mySQL REGEXP match value between commas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related