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.