Where array = value?

Question

It is very difficult to ask this question without giving a proper scenario, so here it goes. I have a MySQL database table with a field that stores an array of numbers. Like so...

id - values
1  - 1,2,3

I need to query against this. Like...

select * from table where values = 3

How to accomplish this?

If this was answered, please point me to the answer because I was unable to find it.

Please note that this is pure MySQL

Thanks for asking but it seems FIND_IN_SET did the trick. I will update my post accordingly if this does not work as expected. — iVoidWarranties, Nov 01 '18 at 00:43

score 1 · Accepted Answer · answered Nov 01 '18 at 00:14

You can try to use FIND_IN_SET function.

CREATE TABLE `table`(
  id int,
  `values` varchar(50)
);


 insert into `table` values (1 ,'1,2,3');

Query 1:

select * from `table` where FIND_IN_SET('3',`values`)

Results:

| id | values |
|----|--------|
|  1 |  1,2,3 |

Eaten by a Grue · Answer 2 · 2018-11-01T00:49:52.543

0

You could use a regex like this:

SELECT * FROM `table` WHERE `values` RLIKE '(^|,)3($|,)'

edited Nov 01 '18 at 00:49

answered Nov 01 '18 at 00:22

Eaten by a Grue

19,208
10
77
99

Is there a reason to prefer this over `FIND_IN_SET`? It's not as clear IMHO. – Barmar Nov 01 '18 at 00:25
No, I think `FIND_IN_SET()` is preferable, just offering up another idea since it's the first thing that came to mind. – Eaten by a Grue Nov 01 '18 at 00:29
1

Works fine: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7140f189f8c9da072afe4f1985ea9af9 – Barmar Nov 01 '18 at 00:38

Where array = value?

2 Answers2