Using REGEXP within MySQL to find a certain number within a comma separated list

Question

I have a list of numbers in some fields in a table, for example something like this:

2033,1869,1914,1913,19120,1911,1910,1909,1908,1907,1866,1921,1922,1923

Now, I'm trying to do a query to check if a number is found in the row, however, I can't use LIKE as then it may return false positives as if I did a search for 1912 in the above field I would get a result returned because of the number 19120, obviously we don't want that - we can't append or prepend a comma as the start/end numbers don't have them.

So, onto using REGEXP I go... I tried this, but it doesn't work (it returns a result):

SELECT * FROM cat_listing WHERE cats REGEXP '[^0-9]*1912[^0-9]*';

I imagine why it still finds something is because of the * quantifier; it found [^0-9] 0 times AFTER 1912 so it considers it a match.

I'm not sure how to modify it to do what I want.

How about not storing comma-separated list in a field? This is what parent-child relationship in a database is for. — Aleks G
– Aleks G, Commented Jun 28, 2015 at 18:06
Did you try with word boundaries: SELECT * FROM cat_listing WHERE cats REGEXP '[[:<:]]1912[[:>:]]';? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 28, 2015 at 18:07
@AleksG I'm aware of that, but for this situation this is the best method and a parent child relationship isn't needed. — Brett
– Brett, Commented Jun 28, 2015 at 18:10
@stribizhev That seems to work great. Feel free to post as an answer. — Brett
– Brett, Commented Jun 28, 2015 at 18:12

souslov · Accepted Answer · 2015-06-28 20:14:10Z

2

In your case, it seems word boundaries are necessary:

SELECT * FROM cat_listing WHERE cats REGEXP '[[:<:]]1912[[:>:]]';

[[:<:]] is the beginning of a word and [[:>:]] is the end. See reference:

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

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 (_).

edited Jun 28, 2015 at 20:14

souslov

44.7k11 gold badges92 silver badges113 bronze badges

answered Jun 28, 2015 at 18:14

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Comments

BentCoder · Accepted Answer · 2015-06-28 18:37:39Z

2

You have another option called find_in_set()

SELECT * FROM cat_listing WHERE find_in_set('1912', cats) <> 0;

Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

answered Jun 28, 2015 at 18:37

BentCoder

12.8k23 gold badges100 silver badges173 bronze badges

2 Comments

Gordon Linoff Over a year ago

This is by far the best answer. I don't know why it was not chosen. But then again, someone who stores lists in comma-separated fields is probably a bit weak on SQL and data representation.

Brett Over a year ago

@GordonLinoff It wasn't chosen because it wasn't available as an answer when I selected the other answer, simple as that. Additionally, you have no idea how I am using this data so don't make assumptions that I should be storing it in another manner.

Abecee · Accepted Answer · 2015-06-28 20:12:57Z

1

No need to use a regex just because the column value has no comma at either end:

SELECT
  cats
FROM cat_listing
WHERE INSTR(CONCAT(',', cats, ','), ',1912,')
;

See it in action: SQL Fiddle.
Please comment if adjustment / further detail is required.

answered Jun 28, 2015 at 20:12

Abecee

2,4032 gold badges14 silver badges20 bronze badges

Collectives™ on Stack Overflow

Using REGEXP within MySQL to find a certain number within a comma separated list

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related