to Search 11 in [1,2,10,11,111,112] by SQL(mysql)

Question

I have a column in a table that contains some number offsets like this:

my_offsets
[1,2,10,11,111,112]

I want query this rows for search value 11 in [1,2,10,11,111,112]. How can I do in SQL(mysql)?

11 in a varchar(299). Can I use IN operator in a string of numbers separated by comma? I don't think but I will test it. Thanks — zabitstack, Dec 15 '22 at 14:17
It seems JSON_CONTAINS('[1,2,3,4,5]','1','$') Returns: 1 is correct answer! I will test it now. — zabitstack, Dec 15 '22 at 14:21
`Can I use IN operator in a string of numbers separated by comma` will not work `[1,2,10,11,111,112]` can be `[1,2,10,11]` or `[11,111,112]` or `[11]` and then you're screwed. — Blag, Dec 15 '22 at 14:21

Michael K · Answer 1 · 2023-02-10T05:31:06.910

0

if data is table

SELECT my_offsets
FROM my_tab
WHERE my_offsets='11'

if data is JSON

SELECT * from my_table
WHERE JSON_CONTAINS(yur_data, '11', '$');

example

  JSON_CONTAINS('[1,2,3]','3','$') Returns: 1
  JSON_CONTAINS('[1,2,3]','7','$') Returns: 0

edited Feb 10 '23 at 05:31

answered Dec 15 '22 at 14:18

Michael K

I don't think is correct because my_offsets contains different numbers [1,2,10,11,112]. '11' is not equals '[1,2,10,11,112]'. – zabitstack Dec 15 '22 at 14:20
make the preconditions clearer than now. var1) my_offsets='[1,2,10,11,111,112]'. var2) column in my_tab my_offsets contains values: 1,2,10,11,111,112. var... Pls show fully what data you have now – Michael K Dec 15 '22 at 14:22
Ok sorry. I edit clearer my question. – zabitstack Dec 15 '22 at 14:23
1

Ok something like this work great: `select JSON_CONTAINS('[1,2,10,11,111,112]','11','$');` – zabitstack Dec 15 '22 at 14:29
1

OK) this is not like "have a column" – Michael K Dec 15 '22 at 14:41

1 Answers1