Обсуждение: "like any" in reverse?

Поиск
Список
Период
Сортировка

"like any" in reverse?

От
"Gauthier, Dave"
Дата:

Hi:

 

create table foo (lst text[]);

insert into foo (lst) values (array[‘jack’,’fred’,’jen’,’sue’]);

 

I want to find all records where any element of lst like ‘j%’. 

 

This does not work...

 

select * from foo where ‘j%’ like any(lst);

 

Intuitively, you’d think....

 

select * from foo where any(lst) like ‘j%’;

 

... but that’s a syntax error.

 

 

Can this be done?

 

Thanks in Advance !

Re: "like any" in reverse?

От
Tom Lane
Дата:
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> I want to find all records where any element of lst like 'j%'.
> This does not work...
> select * from foo where 'j%' like any(lst);

> Intuitively, you'd think....
> select * from foo where any(lst) like 'j%';
> ... but that's a syntax error.

Yeah, the ANY has to be on the right-hand side of the operator.
What you can do for this is build yourself a "reverse like"
operator, ie flip the left and right arguments within the function.
I'm pretty sure there are worked-out examples in the archives
if that's not enough of a hint for you.

            regards, tom lane