CODEWITHSUNDEEP
sqlpostgresql
Sergey Loguntsov
Sergey Loguntsov

Reputation: 68

LIKE operator for array value of column

I have simple table with columns x and y. These columns contains SQL-like patterns for matching. Column x is array of varchar (VARCHAR[]) Column y is simple string (VARCHAR)

As example:

first row:

x                         y
{'asd','sdf%','%er%'}     %er%

I have this query:

SELECT x, 'ters' LIKE ANY("x"), y, 'ters' LIKE "y" FROM s

So the result of this query is:

"{'asd','sdf%','%er%'}";f;"%er%";t

My trouble is:

Why does the LIKE operator work for Y but not for X.

How can I match by VARCHAR[]?

Upvotes: 0

Views: 1220

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125374

You have a wrong input format for the array:

select x, 'ters' like any (x), x[3]
from (values
    ('{asd,sdf%,%er%}'::varchar[]),
    ($${'asd','sdf%','%er%'}$$)
) s(x);
           x           | ?column? |   x    
-----------------------+----------+--------
 {asd,sdf%,%er%}       | t        | %er%
 {'asd','sdf%','%er%'} | f        | '%er%'

https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-INPUT

Upvotes: 1

Related Questions