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:
SELECT * FROM table
WHERE name IN (param);
I know this works but is very very crude:
SELECT * FROM table
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?
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:
SELECT *
FROM mytable
WHERE name = ANY(STRING_TO_ARRAY(param, ','));