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

Question

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.

Answer posted in your suggestion was way too complicated for the question I have posted. The answers this question received are much more to the point and uses SQL features which I wanted. I could have implemented it in a Java method instead of the SQL function in the post you suggested. Thank you for your comment. — user123456789
– user123456789, Commented Oct 14, 2019 at 18:05

user330315 · Accepted Answer · 2019-10-14 17:22:44Z

3

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

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

answered Oct 14, 2019 at 17:22

user330315

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

1 Comment

user123456789 Over a year ago

perfect. I find it amazing it is now I encounter the key word ANY in SQL. Thanks

forpas · Accepted Answer · 2019-10-14 17:21:32Z

3

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

edited Oct 14, 2019 at 17:21

answered Oct 14, 2019 at 17:10

forpas

165k10 gold badges51 silver badges85 bronze badges

2 Comments

forpas Over a year ago

This is a solution for standard sql and I posted it before you tagged with Postgresql. Chek @a_horse_with_no_name's solution if it suits your code.

user123456789 Over a year ago

I gave your answer a +up. Thanks again

shahbaz alam · Accepted Answer · 2020-09-01 20:18:32Z

1

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("");
 };

answered Sep 1, 2020 at 20:18

shahbaz alam

111 bronze badge

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related