GROUP_CONCAT using where in is not working as expected-CodePudding

I got a table [country] as follow

 id  | country_name
 1     India
 2     USA
 3     Nepal
 4     SriLanka

when i try quering as follows it works as expected

select group_concat(country_name) from country where id in (1,2)

i get the result as i want

 India,USA

But when i try the query using this way i get a different result

select GROUP_CONCAT(country_name) from country
where id in (CONVERT(REPLACE(REPLACE('[1,2]','[',''),']',''),CHARACTER));

The result i am getting is

India

Require help in this regard.

CodePudding user response：

you can try this !

select GROUP_CONCAT(country_name) from country
where id in (CONVERT(varchar,REPLACE(REPLACE('[1,2]','[',''),']','')));

CodePudding user response：

I am not sure why you use the second query but you may try using json


select GROUP_CONCAT(country_name) from country where JSON_SEARCH(CAST('["1","2"]' AS JSON), "one", id )

Using json_search mysql will look id in your json array. The problem is, it works with ["1","2"] and not [1,2]