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 stackoverflow.com/questions/36249828/… such as JSON_TABLE or JSON_CONTAINS. Not sure which (if any) is the best option performance wise . See example dbfiddle.uk/… — SOS
– SOS, Commented Mar 9, 2022 at 7:21

Bohemian · Accepted Answer · 2022-03-09 07:23:39Z

2

Use like:

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

answered Mar 9, 2022 at 7:23

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

1 Comment

Well if you're going to be logical about it ... (-;