Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
36http://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, something like:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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, something like:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general