MySQL FIND_IN_SET With Array String

Issue

I have a field in a table I am querying that looks similar to this:

Name          Phone          Category_IDS          Category_Labels
Sample        1111111111     ["1"]                 ["foo", "bar"]

I am trying to use the FIND_IN_SET function to find all rows that contain one of the values listed in the comma separated list. Something like this returns nothing:

SELECT * FROM sampletable WHERE FIND_IN_SET('1', category_ids) <> 0

It does work if I do this:

SELECT * FROM factual_usplaces WHERE FIND_IN_SET('["1"]', category_ids) <> 0

But of course that limits to searches to rows where the category_ids or labels only contains a single value in the comma separated list. So [“1”] would be found but [“1”, “2”] would not.

Is there a way to remove the brackets and quotations from the string on the fly in the query?

Solution

If data is stored exactly how you showed it then you can use REPLACE() to strip double quotes and brackets before feeding category_ids to FIND_IN_SET().

SELECT * 
  FROM Table1 
 WHERE FIND_IN_SET(1, REPLACE(
                        REPLACE(
                          REPLACE(category_ids, '"', ''), 
                        '[', ''), 
                      ']','')) > 0

Here is SQLFiddle


Now if you will use it a lot then you may consider to create a user defined function to simplify your code

CREATE FUNCTION UNQOUTE_LIST(_list VARCHAR(512)) 
RETURNS VARCHAR(512)
RETURN 
REPLACE(
  REPLACE(
    REPLACE(_list, '"', ''), 
  '[', ''), 
']','');

And use it

SELECT * 
  FROM Table1 
 WHERE FIND_IN_SET(1, UNQOUTE_LIST(category_ids)) > 0

Here is SQLFiddle

Answered By – peterm

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published