postgresql match string against list-CodePudding

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?

CodePudding user response：

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.