How to select data from array stored as string in table in MySQL

Question

My team has stored array data as a string in MySQL like below

["1","2","22","11"]

How can we select data from the table where the column contains a certain branch number.

Example of table

sno | Name | Branch 
1.  | Tom. | ["1","2","22"]
2.  | Tim. | ["1","2"]

Can you suggest a query to select all rows containing branch 2?

We tried using FIND_IN_SET() but that is not working as the double quotes and square brackets are also a part of string.

If you mean the column is type `json` this thread has a few suggestions https://stackoverflow.com/questions/36249828/how-to-search-json-array-in-mysql such as JSON_TABLE or JSON_CONTAINS. Not sure which (if any) is the best option performance wise . See example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7b5ab9f506614e08bbb802d0946bfda2 — SOS, Mar 09 '22 at 07:21

score 2 · Accepted Answer · answered Mar 09 '22 at 07:23

2

Use like:

select *
from mytable
where Branch like '%"2"%'

answered Mar 09 '22 at 07:23

Bohemian

1 Answers1