Thread: [GENERAL] Is it possible to specify an ESCAPE for a wildcard search using LIKEANY (...)

I recently stumbled over the need to use a wildcard escape character for a condition that makes use of LIKE ANY,
somethinglike: 

   select *
   from some_table
   where name like any (array['foo_bar%', 'bar_foo%']) escape '/';

so that the underscore wouldn't be treated as a wildard (I can't really change the values _inside_ the array as they
areuser_provided). 

The above throws a syntax error.

So my question is: Is there any way to specify an alternate wildcard escape when using LIKE ANY (..)?

Thomas



Thomas Kellerer <spam_eater@gmx.net> writes:
> So my question is: Is there any way to specify an alternate wildcard escape when using LIKE ANY (..)?

No, not with ESCAPE.  [ manfully resists temptation to run down SQL
committee's ability to design composable syntax ... oops ]

You could do what PG does under the hood, which is to run the pattern
through like_escape():

 select *
   from some_table
   where name like any (array[like_escape('foo_bar%', '/'),
                              like_escape('bar_foo%', '/')]);

If that seems too verbose, maybe build a function to apply like_escape
to each member of an array.

            regards, tom lane


Tom Lane schrieb am 17.01.2017 um 13:41:
> Thomas Kellerer <spam_eater@gmx.net> writes:
>> So my question is: Is there any way to specify an alternate wildcard escape when using LIKE ANY (..)?
>
> No, not with ESCAPE.  [ manfully resists temptation to run down SQL
> committee's ability to design composable syntax ... oops ]
>
> You could do what PG does under the hood, which is to run the pattern
> through like_escape():
>
>  select *
>    from some_table
>    where name like any (array[like_escape('foo_bar%', '/'),
>                               like_escape('bar_foo%', '/')]);
>
> If that seems too verbose, maybe build a function to apply like_escape
> to each member of an array.

OK, thanks.

I was trying to avoid to actually change the input list, but apparently there is no other way.




On Tue, Jan 17, 2017 at 1:45 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Tom Lane schrieb am 17.01.2017 um 13:41:
> Thomas Kellerer <spam_eater@gmx.net> writes:
>> So my question is: Is there any way to specify an alternate wildcard escape when using LIKE ANY (..)?
>
> No, not with ESCAPE.  [ manfully resists temptation to run down SQL
> committee's ability to design composable syntax ... oops ]
>
> You could do what PG does under the hood, which is to run the pattern
> through like_escape():
>
>  select *
>    from some_table
>    where name like any (array[like_escape('foo_bar%', '/'),
>                               like_escape('bar_foo%', '/')]);
>
> If that seems too verbose, maybe build a function to apply like_escape
> to each member of an array.

OK, thanks.

I was trying to avoid to actually change the input list, but apparently there is no other way.

If you don't want to touch the array, you can do something like this:

select *
from tablename as t
where exists (select from unnest($1) as u(x) where t.name like u.x escape '/');
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
On Tue, Jan 17, 2017 at 09:25:38AM +0100, Thomas Kellerer wrote:

> I recently stumbled over the need to use a wildcard escape character for a condition that makes use of LIKE ANY,
somethinglike: 
>
>    select *
>    from some_table
>    where name like any (array['foo_bar%', 'bar_foo%']) escape '/';
>
> so that the underscore wouldn't be treated as a wildard

May I ask for clarification:

Do you need to have the _ NOT be recognized as a wildcard ?

Or do yo need to have the _ be used as an escape character ?

In the latter case I wonder whether the example was an
unlucky choice since neither "b" nor "f" need escaping. Am I
understanding you correctly that you need '_%' to have the
meaning '\%' normally would in the context of a LIKE pattern ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


Karsten Hilbert schrieb am 17.01.2017 um 14:42:
>> I recently stumbled over the need to use a wildcard escape character for a condition that makes use of LIKE ANY,
somethinglike: 
>>
>>    select *
>>    from some_table
>>    where name like any (array['foo_bar%', 'bar_foo%']) escape '/';
>>
>> so that the underscore wouldn't be treated as a wildard
>
> May I ask for clarification:
>
> Do you need to have the _ NOT be recognized as a wildcard ?

Yes, the underscore should NOT be a wildcard in this case.

Now that I think about it - my question actually doesn't make sense.

In order to be able to _use_ an escape character I would need to supply one.
And if I can supply one, I can use the standard one as well.

Sorry for the noise

On Tue, Jan 17, 2017 at 03:27:57PM +0100, Thomas Kellerer wrote:

>> Do you need to have the _ NOT be recognized as a wildcard ?
>
> Yes, the underscore should NOT be a wildcard in this case.

Understood.

So, as Tom hinted at, your best bet might be to write a

    function escape_underscore_in_1dim_text_array(IN TEXT[], OUT TEXT[])
        ...

which takes an array and puts '\' in front of every '_' of
each member such that you can write

    ... like any (escape_underscore_in_1dim_text_array(your_array)) ...

Would that solve the problem ?

(this assume *no* underscore is to be a wildcard in user
provided input, not just some)

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346