mysql in 字符串数组_MySQL FIND_IN_SET带数组字符串

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?

解决方案

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

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