Passing comma separated String to function / stored procedure to use with IN condition

where column = any (string_to_array(replace(delimSources,' ',''),','))
-- replace(source,old, new) => removes whitespace
-- string_to_array(source,separator) => puts the string separated by seperator in an array
-- any => equivalent to IN, BUT in contrast to IN it can take an array, wheras IN takes a comma separated list
-- https://stackoverflow.com/questions/34627026/in-vs-any-operator-in-postgresql

Normally you would write something like this : WHERE column IN (‘bingo’,’bongo’)

But passing comma separated variables into a function does not seem to allow the use of the variable directly in the IN condition. Instead we can convert that string to an array and then check the condition with any().

In MSSQL you seem to have the same problem, and there seem to be 2 kind of workarounds.

  1. Either create a dynamic query and execute that with exec()
  2. Create a tabe valued function

source: http://www.itdeveloperzone.com/2013/03/using-variable-in-in-clause-in-sql.html

Leave a Comment

Your email address will not be published.