CODEWITHSUNDEEP
sqlarrayspostgresql
Igor T.
Igor T.

Reputation: 57

How to search by array values in PostgreSQL query

I have an array: ARRAY['one','mon','uuuu','wed','thu','fri','qwer'] and query:

select * from table t where t.name like ARRAY[i]

how could I iterate query with ARRAY values and save results into some output?

I mean to get results of:

select * from table t where t.name like 'one'
select * from table t where t.name like 'mon'
select * from table t where t.name like 'uuuu'
...
select * from table t where t.name like 'qwer'

and save them for example into the output

Upvotes: 4

Views: 1035

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

One option is to unnest the array in a subquery or CTE and apply the expression you want, e.g.

CREATE TEMPORARY TABLE t (name text);
INSERT INTO t VALUES ('mon'),('qwer'),('mon'),('xpto');

WITH j (val) AS (
 SELECT UNNEST(ARRAY['one','mon','uuuu','wed','thu','fri','qwer'])
) SELECT * FROM j,t WHERE t.name LIKE j.val;

 val  | name 
------+------
 mon  | mon
 mon  | mon
 qwer | qwer
(3 Zeilen)

If you need to use % in your LIKE, just concatenate it to the string with ||, e.g.

WITH j (val) AS (
 SELECT UNNEST(ARRAY['one','mon','uuuu','wed','thu','fri','qwer'])
) SELECT * FROM j,t WHERE t.name LIKE '%' || j.val || '%';

Or use the ANY operator

SELECT * FROM t 
WHERE name LIKE ANY(ARRAY['one','mon','uuuu','wed','thu','fri','qwer']);
 name 
------
 mon
 qwer
 mon
(3 Zeilen)

Upvotes: 2

Related Questions