0

This table has a text column that stores references to some other records in CSV format, for example:

+----+----------------+
| id | linked_folders |
+----+----------------+
| 90 |    NULL        |
| 91 |    NULL        |
| 92 |    123,1,4,40  |
| 93 |    123,1       |
| 94 |    NULL        |
| 95 |    235,8       |
| 96 |    90          |
| 97 |    NULL        |
| 98 |    NULL        |
| 99 |    NULL        |
+----+----------------+

$id = 90;
SELECT * FROM my_table WHERE id = $id OR $id is in linked_folders

Pseudo query above should return rows 90 and 96.

I would like to match records where some exact value I have IS on this field.

I was thinking a LIKE wouldn't work here because I don't know if there is a comma before and/or after.

What alternative do I have?

ESB691
  • 81
  • 5
  • Can you edit your question and add some simple data and the expected output ? – SelVazi Jul 21 '23 at 14:09
  • 1
    You can use the FIND_IN_SET() function - it searches for a value in a comma separated list and returns the index of the value if found. ```select * from table where FIND_IN_SET('9004',your csv values)>0``` – Niveditha S Jul 21 '23 at 14:11
  • @SelVazi sure, I just did! – ESB691 Jul 21 '23 at 14:32
  • @NivedithaS thanks, I wasn't clear enough so I edited my question providing some data, do you think FIND_IN_SET would work here? Doing some tests I can't make it match my expected result. – ESB691 Jul 21 '23 at 14:34
  • `WHERE id = $id OR FIND_IN_SET($id, linked_folders)` should do it. If that doesn't work, please provide the exact query you are trying. – kmoser Jul 21 '23 at 14:42
  • Does this answer your question? [FIND\_IN\_SET on columns? Does it make sense?](https://stackoverflow.com/questions/50759061/find-in-set-on-columns-does-it-make-sense) – kmoser Jul 21 '23 at 14:51

1 Answers1

1

This can be done using FIND_IN_SET :

select * 
from my_table 
where id = 90 or FIND_IN_SET(90, linked_folders) > 0

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29