How to Check if PostgreSQL Array Contains Value or Substring of Value-CodePudding

I want to make a query that displays the data of the table for which column x contains the value '11'. Column x being an array.

I find:

x = ['2114', '111', '321', '11']

Select * 
from table_1 WHERE '11' = ANY(x)

but this match only for '11', instead I wish this will match for '2114' '111' and '11'

I also have a problem because I need to create this query dynamically.

if condition:
    return f" %s = ANY(x)"
else:
    return f"{field_name} like %s"  ## '_' || %s || '_'

For else condition, if I write '%' || %s || '%', I receive an error: IndexError: tuple index out of range

CodePudding user response：

I think your only option is an EXISTS condition:

Select t.*
from table_1 t
WHERE exists (select *
              from unnest(t.x) as u(element)
              where u.element like '%')

CodePudding user response：

You can do it like this (Result here)

Select x,array_to_string(x,';')
from table_1 
WHERE array_to_string(x,';') like '%'