Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: <pgsql-sql@postgresql.org>
Subject: Regular Expression Match Operator escape character
Date: Wed, 8 Dec 2010 19:21:44 +0530
Organization: Zoniac Software Private Limited
Message-ID: <006601cb96df$0fd82cd0$2f888670$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcuW3wwSwMJVFTn8S+SkJv9U2Kubow==
Content-Language: en-us

Hi,

We're running PostgreSQL v8.2.3 on RHEL5.

In some places in our application, we use Regular Expression Match Operator
(~* => Matches regular expression, case insensitive) inside WHERE criteria.

Example:
SELECT ... 
FROM ...
WHERE (SKILLS ~*
'(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)C#(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)' 
	OR SKILLS ~*
'(^|\\^|\\||[^0-9|^a-z|^A-Z]|$).NET(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)')

In this case, we're trying to search/match for either "C#" OR ".NET" in
SKILLS column.

My question here is, do I need to escape the characters "#" and "."  here?

Regards,
Gnanam