CODEWITHSUNDEEP
postgresqlescapingsql-like
Bebeoix
Bebeoix

Reputation: 589

How escape without using replace function in LIKE with PostgreSQL

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.

Upvotes: 1

Views: 2241

Answers (2)

Tometzky
Tometzky

Reputation: 23890

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)
    ||
    '%';

Upvotes: 4

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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

Upvotes: 2

Related Questions