Search code examples
sqlarrayspostgresqlin-clause

SQL how to convert array to values for use in IN clause

Working in Postgres SQL:

create table TAB1 ( X int, Y varchar(12));

insert into TAB1 values (1, 'ABC');
insert into TAB1 values (2, 'BCD');
insert into TAB1 values (3, 'EFG');

My query argument comes in as a comma separated string: 'ABC,BCD'

I am trying to construct the query below, but getting an error:

select * 
from TAB1 
where Y in (STRING_TO_ARRAY('ABC,BCD', ','));

ERROR:

Operator does not exist: character varying = text[]

My question is how to convert 'ABC,BCD' to a list of values to use in the IN CLAUSE. Prefer answer in SQL query, not method or function. Thanks.

Solution

  • With an array, you need to use the ANY operator:

    select * 
from TAB1 
where Y = any( STRING_TO_ARRAY('ABC,BCD', ',') );