Thread: [GENERAL] Is it possible to specify an ESCAPE for a wildcard search using LIKEANY (...)
[GENERAL] Is it possible to specify an ESCAPE for a wildcard search using LIKEANY (...)
From
Thomas Kellerer
Date:
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
Re: [GENERAL] Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)
From
Tom Lane
Date:
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
[GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search usingLIKE ANY (...)
From
Thomas Kellerer
Date:
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.
Re: [GENERAL] Re: Is it possible to specify an ESCAPE for a wildcardsearch using LIKE ANY (...)
From
Vik Fearing
Date:
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
Re: [GENERAL] Is it possible to specify an ESCAPE for a wildcardsearch using LIKE ANY (...)
From
Karsten Hilbert
Date:
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
[GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search usingLIKE ANY (...)
From
Thomas Kellerer
Date:
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
Re: [GENERAL] Re: Is it possible to specify an ESCAPE for a wildcardsearch using LIKE ANY (...)
From
Karsten Hilbert
Date:
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