postgresql match string against list

Question

Assuming I have this table and I am trying to match a substring from my columns (col) such as 'aa' to a list or array ('aaa, bbb, 'ccc', 'aab'), how would I go about doing this?

I have tried these with no results from the query when I am expecting at least 1 row that contains 'aa' in column col:

SELECT * FROM my_table WHERE col IN ('aaa', 'bbb', 'ccc', 'aab');
SELECT * FROM my_table WHERE col = ANY(ARRAY['aaa', 'bbb', 'ccc', 'aab']);

Any way to make a query equivalent to the python statement:

if any('aa' in item for item in ['aaa', 'bbb', 'ccc', 'aab']):

To return the row matching?

"no luck" isn't a valid Postgres error message. What exactly didn't work? — user330315
– user330315, Commented Apr 21, 2022 at 13:31
I edited it, I did not get a return on the row that contains 'aa' in col — cozzymotto
– cozzymotto, Commented Apr 21, 2022 at 13:33
Well, obviously not. You are only looking for 'aaa' which is a different value than 'aa' — user330315
– user330315, Commented Apr 21, 2022 at 13:36
I would like my col string 'aa' to match anything within that list. For example, a python if statement: if any('aa' in item for item in ['aaa', 'bbb']) would be true. — cozzymotto
– cozzymotto, Commented Apr 21, 2022 at 13:44
select * from my_table where position(col in ARRAY_TO_STRING(ARRAY['ccc','bbb','aaa'],','))>0; — Luuk
– Luuk, Commented Apr 21, 2022 at 13:49

Luuk · Accepted Answer · 2022-04-21 14:30:53Z

2

select * 
from my_table 
where position(col in ARRAY_TO_STRING(ARRAY['ccc','bbb','aaa'],','))>0;

SELECT ARRAY_TO_STRING(ARRAY['ccc','bbb','aaa'],','); will return a string "ccc,bbb,aaa"
POSITION("aa" in "ccc,bbb,aaa") will return the position of "aa" in that string.

EDIT: An alternative way (how many postgresql functions can I use in one statement):

select col 
from my_table
cross join (
   select s[x] as value 
   from (select  ARRAY['ccc','bbb','aaa']::text[] s) t 
   cross join generate_series(1,array_length(ARRAY['ccc','bbb','aaa'],1)) x
) a
where position(col in a.value)>0;

The sub-query will generate a table a, with column value, which contains all the values of the table.

There is a DBFIDDLE of both queries.

edited Apr 21, 2022 at 14:30

answered Apr 21, 2022 at 13:52

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

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

2 Comments

cozzymotto Over a year ago

That worked! Thank you for the solution and explanation @Luuk

Nagarjun Nagesh Over a year ago

worked perfectly to check for any string match given in the array length. Thank you very much.

Collectives™ on Stack Overflow

postgresql match string against list

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related