How escape without using replace function in LIKE with PostgreSQL

Question

Short version: I need a function to escape strings for use in LIKE expressions, so fr_e%d becomes fr\_e\%d.

I'm looking for escape the result of a query without the need to use replace. Because in this result I have path with this char _ inside. And because I use this result in a LIKE statement, it make the "_" a joker like start *.

I have to use LIKE because I'm doing some stuff concatenation in this same statement who need the fact that I use LIKE.

I tried ~ to replace LIKE but I will have the same problem with the char '.' because ~ is to use regex just skip this solution too.

Tometzky · Accepted Answer · 2012-11-07T09:47:38.960

4

create function quote_like(text) returns text language sql immutable strict as
  $quote_like$
    select regexp_replace($1, $$[_%\\]$$, $$\\\&$$, 'g');
  $quote_like$;

This function prepends all occurrences of _, % and \ in provided string with \. You can use it for example like this:

select * from tablename
  where tablecolumn like
    '%'
    ||
    (select quote_like(substring) from substrings where substring_id=123)
    ||
    '%';

edited Nov 07 '12 at 09:47

answered Nov 06 '12 at 23:19

Tometzky

22,573
5
59
73

A single pass with `regexp_replace()` - even a clean, simple one like in the example - is regularly more expensive than three instances of `replace()`. – Erwin Brandstetter Nov 07 '12 at 00:19
@ErwinBrandstetter: you're right — `replace(replace(replace(...` is somewhat faster than single `regexp_replace`, but not by much — 15-20% in my tests. – Tometzky Nov 07 '12 at 09:55
Thanks all for your solutions. – Bebeoix Nov 08 '12 at 21:15

Erwin Brandstetter · Answer 2 · 2012-11-07T00:17:56.913

Update

Triggered by @Tometzky's claim:

Using regexp_replace is better than simply replace (...)

I have to disagree. Regular expression functions are powerful but comparatively slow.
Therefore, event 3x replace() is faster than a single, simple regexp_replace() - at least in my tests:

CREATE OR REPLACE FUNCTION quote_like2(text)
  RETURNS text LANGUAGE SQL IMMUTABLE STRICT AS
$func$
SELECT replace(replace(replace(
         $1
        ,'\', '\\') -- must come first
        ,'_', '\_')
        ,'%', '\%');
$func$;

Note that I use plain single quotes instead of dollar quoting. This requires standard_conforming_strings = on, which is the default since PostgreSQL 9.1.

Try both functions with your data:

EXPLAIN ANALYZE SELECT quote_like1(col) from tbl;

Original answer

Contrary to you question title, you would normally use replace() or a similar string function to prepare your pattern. Consider this demo:

SELECT 'abcde' LIKE ('abc_' || '%')  -- TRUE
      ,'abc_e' LIKE ('abc_' || '%')  -- TRUE
      ,'abcde' LIKE (replace('abc_', '_', '\_')  || '%')   -- FALSE
      ,'abc_e' LIKE (replace('abc_', '_', '\_')  || '%');  -- TRUE

You're right too, especially about what solution is faster. Thanks. — Bebeoix, Nov 08 '12 at 21:14

How escape without using replace function in LIKE with PostgreSQL

2 Answers2

Update

Original answer