Split varchar value used in WHERE clause

Question

I am passing a list of string from c# code concatenated to a varchar variable in MySQL, separated by ",".

I want to split the varchar and use it in my WHERE clause like the sample code below:

SELECT * FROM table_name WHERE column_name IN split(varchar_variable, ",")

Do mysql have function as the split function that returns an array? Or if you have some suggestions on how to do it right, thank you in advance.

In MySQL you can use `FIND_IN_SET` but it would be easier just to put the string directly into the query. — Nick, Apr 08 '19 at 07:03
Possible duplicate of [MySQL query finding values in a comma separated string](https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) — Nick, Apr 08 '19 at 07:03

score 0 · Answer 1 · answered Apr 08 '19 at 07:11

Unfortunately there is no such function.

You can do some workaround: if you have ilst of values like

value1,value2,value3

Then it's enough to look for ,value1, using LIKE operator. n order for this to work you need to pad list with commas:

,value1,value2,value3,

So your query would become:

SELECT *
FROM table_name
WHERE ',' + varchar_variable + ',' LIKE ',' + column_name + ','

er-sho · Answer 2 · 2019-04-08T07:55:59.743

If you want to pass multiple values inside WHERE clause with your SELECT statement then you need to modify your WHERE clause parameter values like

string values = "'\'Albania\',\'Andorra\''";

If you debug this code then it will look like,

''Albania','Andorra''

And in MySql editor you can pass same values like,

SET @myArray := '\'Albania\',\'Andorra\'';

So finally your SELECT statement will be,

SET @myArray := '\'Albania\',\'Andorra\'';

SET @sql = CONCAT('SELECT CountryID, CountryName FROM Countries WHERE CountryName IN (', @myArray, ')');

PREPARE statement FROM @sql;
EXECUTE statement;

Output:

Split varchar value used in WHERE clause

2 Answers2