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

Question

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

can you post the script you've tried ?

Philippe
– Philippe

2022-03-14 14:17:29 +00:00
Commented Mar 14, 2022 at 14:17 — Philippe
– Philippe, Commented Mar 14, 2022 at 14:17

user330315 · Accepted Answer · 2022-03-14 14:05:57Z

1

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 '%11%')

answered Mar 14, 2022 at 14:05

user330315

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

Comments

Philippe · Accepted Answer · 2022-03-14 14:07:12Z

1

You can do it like this (Result here)

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

answered Mar 14, 2022 at 14:07

Philippe

1,8376 silver badges19 bronze badges

4 Comments

rafaelelmundo Over a year ago

Wow, awesome. But now I have another problem.. I need to replace '11' with %s because I made this query dynamically. When I write instead '%11%' , '%' || %s || '%', I receive an error because yeah... list index out of range. I don't know how to solve this. I need %s to replace it with the appropriate value, but I also need %, which leads to an even conflict.

Philippe Over a year ago

You want to build your query in a function ?

rafaelelmundo Over a year ago

Something like that. It is more code.. But yeah.. Something like: ".... from... where.. %s = ..., %s = ..., array_to_string(x, ';') like '%' || %s || '%' .format(12, 2, 3)

Philippe Over a year ago

if you build it in a function you have to use execute` statement. You create you string with $$ and then execute my_string

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related