Escape string for LIKE op

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

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

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

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

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.

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