CODEWITHSUNDEEP
sqlregexpostgresql
Celeritas
Celeritas

Reputation: 15091

How do you escape this regular expression?

I'm looking for

"House M.D." (2004)

with anything after it. I've tried where id~'"House M\.D\." \(2004\).*'; and there's no matches

This works id~'.*House M.D..*2004.*'; but is a little slow.

Upvotes: 2

Views: 614

Answers (3)

Ωmega
Ωmega

Reputation: 43703

CREATE OR REPLACE FUNCTION public.regexp_quote(IN TEXT) 
  RETURNS TEXT 
  LANGUAGE plpgsql 
  STABLE 
AS $$ 
/******************************************************************************* 
 * Function Name: regexp_quote 
 * In-coming Param: 
 *   The string to decoded and convert into a set of text arrays. 
 * Returns: 
 *   This function produces a TEXT that can be used as a regular expression 
 *   pattern that would match the input as if it were a literal pattern. 
 * Description: 
 *   Takes in a TEXT in and escapes all of the necessary characters so that 
 *   the output can be used as a regular expression to match the input as if 
 *   it were a literal pattern. 
 ******************************************************************************/ 
BEGIN 
  RETURN REGEXP_REPLACE($1, '([[\\](){}.+*^$|\\\\?-])', '\\\\\\1', 'g'); 
END; 
$$

Test:

SELECT regexp_quote('"House M.D." (2004)'); -- produces: "House M\\.D\\." \\(2004\\)

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 325141

I suspect you're on an older PostgreSQL version that interprets strings in a non standards-compliant C-escape-like mode by default, so the backslashes are being treated as escapes and consumed. Try SET standard_conforming_strings = 'on';.

As per the lexical structure documentation on string constants, you can either:

  • Ensure that standard_conforming_strings is on, in which case you must double any single quotes (ie ' becomes '') but backslashes aren't treated as escapes:

    id ~ '"House M\.D\." \(2004\)'

  • Use the non-standard, PostgreSQL-specific E'' syntax and double your backslashes:

    id ~ E'"House M\\.D\\." \\(2004\\)'

PostgreSQL versions 9.1 and above set standard_conforming_strings to on by default; see the documentation.

You should turn it on in older versions after testing your code, because it'll make updating later much easier. You can turn it on globally in postgresql.conf, on a per-user level with ALTER ROLE ... SET, on a per-database level with ALTER DATABASE ... SET or on a session level with SET standard_conforming_strings = on. Use SET LOCAL to set it within a transaction scope.

Upvotes: 4

closh
closh

Reputation: 313

Looks that your regexp is ok

http://sqlfiddle.com/#!12/d41d8/113

Upvotes: 2

Related Questions