MySQL FIND_IN_SET With Array String

Question

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?

Very strange data in table. And why are they wrapped in [...]? I would suggest you to read an article about the database normalizing. — Devart
– Devart, Commented Sep 10, 2013 at 5:01
The table is actually not originated by me, its a company called Factual that is quite widely known and used. Its strange that they wrap in brackets to me too... — ryandlf
– ryandlf, Commented Sep 10, 2013 at 5:06

peterm · Accepted Answer · 2013-09-10 05:29:05Z

8

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

edited Sep 10, 2013 at 5:29

answered Sep 10, 2013 at 5:14

peterm

93k16 gold badges157 silver badges162 bronze badges

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

3 Comments

ryandlf Over a year ago

Replace was the key here. So you can't replace multiple characters with one replace function I assume?

peterm Over a year ago

No, unfortunately you can't.

HG Webs Over a year ago

This is not working when I'm searching on a string so I'm posting a new answer which works for me.

Shivam · Accepted Answer · 2013-09-10 05:12:46Z

1

try below sql query with like operator

SELECT * FROM factual_usplaces WHERE category_ids LIKE '%1,2%'

Hope This help you

answered Sep 10, 2013 at 5:12

Shivam

2,44319 silver badges31 bronze badges

2 Comments

ryandlf Over a year ago

Like won't work. I need to use the FIND_IN_SET function otherwise it will cause a load of other problems.

Shivam Over a year ago

SELECT * FROM factual_usplaces WHERE ( FIND_IN_SET( '1', category_ids ) ) OR ( FIND_IN_SET( '2', category_ids ) )

HG Webs · Accepted Answer · 2022-08-31 06:30:38Z

0

SELECT * 
  FROM Table1 
 WHERE FIND_IN_SET("bar", REPLACE(REPLACE(REPLACE(`Category_Labels`, '\"', ''), '[', ''), ']',''))

This is working for me

answered Aug 31, 2022 at 6:30

HG Webs

651 silver badge8 bronze badges

Collectives™ on Stack Overflow

MySQL FIND_IN_SET With Array String

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related