Suman Suman - 2 years ago 98
SQL Question

How to multi-split a string (WHERE IN query)

I am passing a string as param (via massivejs) into my query. The string is formatted as:

param = 'red, blue, green'
. The param itself does not have a fixed length (',' being the delimiter) as it is populated through what the user sends in (but is maxed out at 10 elements).

How would I break the string down into individual strings inside my query?

Eg of what I am trying to do:

WHERE name IN (param);

I know this works but is very very crude:

WHERE name IN (split_part(param, ',', 1), split_part(param, ',', 2) .......)) -- keep going.

Essentially I want to have ('red', 'blue', 'green' ....) inside the IN parenthesis. Is there a nicer way of accomplishing this?

Answer Source

You could use the string_to_array function to split the string to an array and then use the any function to check if your element is contained in it:

FROM   mytable
WHERE  name = ANY(STRING_TO_ARRAY(param, ','));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download