MySQL: User Defined Function Used in WHERE x IN (...) Not working

Question

I have a stored procedure in MySQL that takes a single parameter that can consist of 1 or more values. I want to be able to filter by these values, so I created a function that can take a string and a delimiter parameter

my_func('val1,val2,val3',',')

and it returns a string

'val1','val2','val3'

. I am then calling this function in the where clause such as

WHERE x IN (my_func('val1,val2,val3',','))

But this is not working. It does not give any error, but it keeps running without ever returning anything. I have tested the function individually and it works fine and returns in less than a second. The query I am trying to run it in is a test query that is very basic. Replacing the function with a regular string such as

WHERE x IN ('val1','val2','val3')

works perfectly fine and returns in just a couple seconds. Is what I am trying to do even possible? Thanks!

I am confused to what your stored proc is supposed to be doing. — FutbolFan, Jul 28 '15 at 15:47
@FutbolFan It takes a comma-delimited list and returns a comma-delimited list with quotes around each element. — Barmar, Jul 28 '15 at 15:48

score 4 · Accepted Answer · answered Jul 28 '15 at 15:48

4

IN requires the argument to be a literal list, it doesn't re-parse the string. Use FIND_IN_SET:

WHERE FIND_IN_SET(x, 'val1,val2,val3'))

answered Jul 28 '15 at 15:48

Barmar

741,623
53
500
612

Wow this answer is so underrated. – darma Apr 20 '20 at 19:39

MySQL: User Defined Function Used in WHERE x IN (...) Not working

1 Answers1