CODEWITHSUNDEEP
sqlarrayspostgresqlin-clause
user123456789
user123456789

Reputation: 83

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.

Upvotes: 1

Views: 12665

Answers (3)

shahbaz alam
shahbaz alam

Reputation: 11

Use this utility function which converts array to comma separated values to be used inside IN clause:

const arrayToInClauseString = (arrayData) => {
   return arrayData
     .map((item, index, all) => {
       if (index === all.length - 1) {
         return `"${item}"`;
       } else {
         return `"${item}",`;
       }
     })
     .join("");
 };

Upvotes: 1

user330315
user330315

Reputation:

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

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

Upvotes: 3

forpas
forpas

Reputation: 164069

The simplest way to get the results that you want is instead of IN to use the operator LIKE:

select * from TAB1
where ',' || 'ABC,BCD' || ',' like '%,' || Y || ',%'

replace the concatenation operator || with the operator that works in your db, (like + for SQL Server) or the function concat().
See the demo.
Results:

>  X | Y  
> -: | :--
>  1 | ABC
>  2 | BCD

Upvotes: 3

Related Questions