Re: [GENERAL] Escape string for LIKE op
On 8/15/13, Jeff Janes wrote: > On Thu, Aug 15, 2013 at 1: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. > > 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
Re: [GENERAL] Escape string for LIKE op
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
Re: [GENERAL] Escape string for LIKE op
On Thu, Aug 15, 2013 at 1: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. 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
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general