postgresql match string against list

April 21, 2022

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?

>Solution :

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.