Re: Escape string for LIKE op

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.

Cheers,

Jeff