MYSQL Find In Set with Array Data

Question

I have a Database that looks like this (unfortunately cannot be changed)

patients (table)
name    |   companies
-----------------------
Form 1  |     [8,3]
Form 2  |     [8]

I want to select every form with an assignment of 8 so I tried this:

SELECT * FROM patients WHERE FIND_IN_SET(patients, 8)

But it's not working, I tried another test in a sample database and if the [ and ] is removed it works just fine, unfortunately the [ and ] cannot be removed in the live database (this data is stringified from JSON)

I think you need stackoverflow.com/questions/27448897/… but for this you need mysql 5.7+ version. Will not work in mysqk 5.6 or minor. — user3145373 ツ
– user3145373 ツ, Commented Aug 19, 2017 at 4:27

Rohan · Accepted Answer · 2017-08-19 04:32:53Z

4

You can use REPLACE() to strip [ and ] brackets before using companies in FIND_IN_SET.

SELECT * 
  FROM patients 
 WHERE FIND_IN_SET(8, REPLACE(
                        REPLACE(
                          companies, 
                        '[', ''), 
                      ']',''))

SQLFiddle

Please refer this question for the same scenario.

answered Aug 19, 2017 at 4:32

Rohan

3,0881 gold badge23 silver badges27 bronze badges

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

2 Comments

Jordash Over a year ago

Does this modify the values in the db?

Rohan Over a year ago

@Jordash No, it should not.

Collectives™ on Stack Overflow

MYSQL Find In Set with Array Data

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related