1

I have a postgres query with one input parameter of type varchar.

value of that parameter is used in where clause.

Till now only single value was sent to query but now we need to send multiple values such that they can be used with IN clause.

Earlier

  value='abc'.

  where data=value.//current usage

now

  value='abc,def,ghk'.

  where data in (value)//intended usage

I tried many ways i.e. providing value as 

  value='abc','def','ghk'

Or

  value="abc","def","ghk" etc.

But none is working and query is not returning any result though there are some matching data available. If I provide the values directly in IN clause, I am seeing the data.

I think I should somehow split the parameter which is comma separated string into multiple values, but I am not sure how I can do that.

Please note its Postgres DB.

user2654241
  • 167
  • 1
  • 2
  • 13

1 Answers1

3

You can try to split input string into an array. Something like that:

where data = ANY(string_to_array('abc,def,ghk',','))
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • Thanks Igor, I tried using ANY(string_to_array) as you answered however seems I don't have correct version of postgres. Though running select version() shows 15.3 but upon running ANY(string_to_array) statement I see below error. Unknown method string_to_array – user2654241 Mar 14 '16 at 16:20
  • @user2654241 There is no postgres version `15.3`. Are you shure you are using postgres and not one of its forks? – Ihor Romanchenko Mar 14 '16 at 16:23
  • I am using it from within Labkey's query schema browser. You are right though select version() shows 15.3 but I also got confused with it. Anyway I will try basic query and see if I can use string_to_array anyway. – user2654241 Mar 14 '16 at 16:27
  • There are other similar functions. Like `regexp_split_to_array`. Or you can write such function yourself. – Ihor Romanchenko Mar 14 '16 at 16:29