Re: Escape string for LIKE op

On 8/15/13, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Thu, Aug 15, 2013 at 1:16 PM, Robert James <srobertjames(at)gmail(dot)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.