3

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?

Suman
  • 43
  • 5

1 Answers1

1

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, ','));
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    Works perfectly Thanks! A quick followup: how would I tweak this to handle integers? That is if the table had integer numbers. Is something like to_number applicable here? – Suman Aug 21 '16 at 07:09
  • 1
    @SumanBhattarai In such a case, I'd cast the column to string, so you're comparing apples to apples. AFAIK, Postgres doesn't have a convenient way to apply a function to all the elements in an array. – Mureinik Aug 21 '16 at 07:16