Обсуждение: [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 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