Thread: Escape string for LIKE op

How can I escape a string for LIKE operations?

I want to do:

SELECT * FROM t WHERE a LIKE b || '%'

But I want be to interpreted literally.  If b is 'The 7% Solution', I
don't want that '%' to be wildcard.  I can't find an appropriate
function to escape it and any other potential wildcards for LIKE
clauses.

On Thu, Aug 15, 2013 at 1:16 PM, Robert James <srobertjames@gmail.com> wrote:
> How can I escape a string for LIKE operations?
>
> I want to do:
>
> SELECT * FROM t WHERE a LIKE b || '%'
>
> But I want be to interpreted literally.  If b is 'The 7% Solution', I
> don't want that '%' to be wildcard.  I can't find an appropriate
> function to escape it and any other potential wildcards for LIKE
> clauses.

You could use the replace function.

select 'The 7% Solution is a good book' like replace('The 7%
Solution', '%', '\%')||'%';
true

select 'The 7pt Solution is a good book' like replace('The 7%
Solution', '%', '\%')||'%';
false

If you need to worry about underscores as well, you could chain two
replace functions together.

Cheers,

Jeff

On 08/15/2013 10:16 PM, Robert James wrote:
> How can I escape a string for LIKE operations?
>
> I want to do:
>
> SELECT * FROM t WHERE a LIKE b || '%'
>
> But I want be to interpreted literally.  If b is 'The 7% Solution', I
> don't want that '%' to be wildcard.  I can't find an appropriate
> function to escape it and any other potential wildcards for LIKE
> clauses.

In this particular case, you're better off changing the query to be

SELECT * FROM t WHERE a >= b;

but if your needs are more complex than your actual question, you'll
most likely need to process b like Jeff explained.
--
Vik

On 8/15/13, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Thu, Aug 15, 2013 at 1:16 PM, Robert James <srobertjames@gmail.com>
> wrote:
>> How can I escape a string for LIKE operations?
>>
>> I want to do:
>>
>> SELECT * FROM t WHERE a LIKE b || '%'
>>
>> But I want be to interpreted literally.  If b is 'The 7% Solution', I
>> don't want that '%' to be wildcard.  I can't find an appropriate
>> function to escape it and any other potential wildcards for LIKE
>> clauses.
>
> You could use the replace function.
>
> select 'The 7% Solution is a good book' like replace('The 7%
> Solution', '%', '\%')||'%';
> true
>
> select 'The 7pt Solution is a good book' like replace('The 7%
> Solution', '%', '\%')||'%';
> false
>
> If you need to worry about underscores as well, you could chain two
> replace functions together.

This is my concern - it's never a good idea to try to find all escape
chars by hand.  You end up missing one.  Think about complicated cases
where the escape char is escaped itself - my experience is that a
regex to escape a string never works in every case.  You need to use a
real parser - which of course Postgres already has, that's how it
interprets the string in the first place.