Convert varchar parameter with CSV into column values postgres

Question

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.

Possible duplicate: stackoverflow.com/questions/31191507/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 14, 2016 at 21:29

Ihor Romanchenko · Accepted Answer · 2016-03-14 15:59:28Z

3

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

where data = ANY(string_to_array('abc,def,ghk',','))

answered Mar 14, 2016 at 15:59

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2654241 Over a year ago

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

Ihor Romanchenko Over a year ago

@user2654241 There is no postgres version 15.3. Are you shure you are using postgres and not one of its forks?

user2654241 Over a year ago

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.

Ihor Romanchenko Over a year ago

There are other similar functions. Like regexp_split_to_array. Or you can write such function yourself.

Collectives™ on Stack Overflow

Convert varchar parameter with CSV into column values postgres

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related