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-07 09:47:38Z

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 7, 2012 at 9:47

answered Nov 6, 2012 at 23:19

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

A single pass with regexp_replace() - even a clean, simple one like in the example - is regularly more expensive than three instances of replace().

Tometzky Over a year ago

@ErwinBrandstetter: you're right — replace(replace(replace(... is somewhat faster than single regexp_replace, but not by much — 15-20% in my tests.

Bebeoix Over a year ago

Thanks all for your solutions.

Erwin Brandstetter · Accepted Answer · 2012-11-07 00:17:56Z

2

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

edited Nov 7, 2012 at 0:17

answered Nov 6, 2012 at 21:28

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

1 Comment

Bebeoix Over a year ago

You're right too, especially about what solution is faster. Thanks.

Collectives™ on Stack Overflow

How escape without using replace function in LIKE with PostgreSQL

2 Answers 2

3 Comments

Update

Original answer

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Update

Original answer

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related