Skip site navigation (1) Skip section navigation (2)

Re: Generic regex escape function?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>
Cc: "Jon Asher" <jon(at)vagabond-software(dot)com>, sfpug(at)postgresql(dot)org
Subject: Re: Generic regex escape function?
Date: 2004-11-17 19:03:48
Message-ID: 200411171103.48939.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
David,

> The...what? What is "tri-mode regex support"?

PostgreSQL has "Advanced", "Basic" and "Extended".   Basic and extended are 
Posix forms, and "Advanced" is most similar to Perl's regex (and is also the 
default).   You can set this through the regex_flavor GUC variable, or via 
escape codes in the regex itself.

> And what if 
> <some-user-string> has a single quote (') in it?

Then you need to use "quote_literal".  Except that quote_literal also includes 
the beginning an terminating ' on the string, which is not so useful.   So I 
wrote a little function called double_quote(text) which uses quote_literal 
but strips off the 1st and last '.

So, a user-input-proof regex comparison would be:

some_column ~* ( '***=' || double_quote(user_input_var))

see:
http://www.postgresql.org/docs/7.4/static/functions-matching.html#FUNCTIONS-POSIX-TABLE

Of course, this makes me inclined to simply use ILIKE and leave regex out of 
it ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

sfpug by date

Next:From: Josh BerkusDate: 2004-11-18 19:16:11
Subject: Poor, bored?
Previous:From: David WheelerDate: 2004-11-17 18:38:44
Subject: Re: Generic regex escape function?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group