X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id A85AB9FA3B4
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 15 Jul 2006 19:03:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 75127-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 15 Jul 2006 22:03:26 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.187])
	by postgresql.org (Postfix) with ESMTP id C823B9F95E3
	for <pgsql-sql@postgresql.org>; Sat, 15 Jul 2006 19:03:25 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id p77so166257nfc
	for <pgsql-sql@postgresql.org>; Sat, 15 Jul 2006 15:03:23 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth;
	b=nfjU3yMROw8jcZizw3byuwVC1jvW7aZCv8fKovJ9XWlhe9WYxPQ3urhmjzbMErzkWA+udJLpFNgI3FMKL+yytVZidPExgLKHjZBbwShXMXl4g60MXFMjk//JXZOMwzpC2IkCj4PC7DBf79BdLZmO9fxECr/QCDXNPrKDxn9iesY=
Received: by 10.78.166.7 with SMTP id o7mr273401hue;
	Sat, 15 Jul 2006 15:03:23 -0700 (PDT)
Received: by 10.78.29.15 with HTTP; Sat, 15 Jul 2006 15:03:23 -0700 (PDT)
Message-ID: <bf05e51c0607151503j53fffb01y38840a72c43a50f6@mail.gmail.com>
Date: Sat, 15 Jul 2006 17:03:23 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Postgres SQL language list" <pgsql-sql@postgresql.org>
Subject: Regular Expression in SQL
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_42718_7764933.1153001003408"
X-Google-Sender-Auth: fe7b304b170f9480
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/216
X-Sequence-Number: 25790

------=_Part_42718_7764933.1153001003408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I recall not long ago a discussion about regular expressions in a query that
hit on this exact topic but don't think it was ever resolved so I am giving
it a go again...

Here is my query (keep in mind that I am just experimenting now so don't
worry about the fact that I am using nested substring function calls):

SELECT
    referrer,
    substring(referrer FROM '^([^\\/]*\\/\\/[^\\/]*)(\\/)?'),
    substring(referrer FROM
'^[^\\/]*\\/\\/www\\.google\\.[^\\/]*\\/[^\\?]*\\?(.*)$'),
    substring(substring(referrer FROM
'^[^\\/]*\\/\\/www\\.google\\.[^\\/]*\\/[^\\?]*\\?(.*)$') FROM
'((%&q=)|(q=))#"[^&]*#"((&%)|())' FOR '#')
FROM one_hour_air.web_page_view
WHERE referrer ~ '^[^\\/]*\\/\\/(www.google\\.[^\\/]*)\\/'

What I get is:

referrer
substring
substring_1
substring_2

http://www.google.ca/search?q=one+hour+heating&hl=en
http://www.google.ca
q=one+hour+heating&hl=en
q=

http://www.google.com/search?hl=en&q=One+hour+heating+and+Air
http://www.google.com
hl=en&q=One+hour+heating+and+Air
hl=en&q=

What I expected for substring_2 was (respectively):

one+hour+heating

One+hour+heating+and+Air

I thought by using the FOR '#' I could specify exactly what part of the
expression I would get but it still grabs the first (...) of the pattern.
At least that is what the documentation in seciton 9.7.2 at
http://www.postgresql.org/docs/8.1/static/functions-matching.html led me to
believe.  How can I get the part of the string I am really after without
using one nested substring after another?

Thanks,
Aaron Bono

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

------=_Part_42718_7764933.1153001003408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I recall not long ago a discussion about regular expressions in a query that hit on this exact topic but don't think it was ever resolved so I am giving it a go again...<br><br>Here is my query (keep in mind that I am just experimenting now so don't worry about the fact that I am using nested substring function calls):
<br><br>SELECT<br>&nbsp;&nbsp;&nbsp; referrer,<br>&nbsp;&nbsp;&nbsp; substring(referrer FROM '^([^\\/]*\\/\\/[^\\/]*)(\\/)?'),<br>&nbsp;&nbsp;&nbsp; substring(referrer FROM '^[^\\/]*\\/\\/www\\.google\\.[^\\/]*\\/[^\\?]*\\?(.*)$'),<br>&nbsp;&nbsp;&nbsp; substring(substring(referrer FROM '^[^\\/]*\\/\\/www\\.google\\.[^\\/]*\\/[^\\?]*\\?(.*)$') FROM '((%&amp;q=)|(q=))#&quot;[^&amp;]*#&quot;((&amp;%)|())' FOR '#')
<br>FROM one_hour_air.web_page_view<br>WHERE referrer ~ '^[^\\/]*\\/\\/(www.google\\.[^\\/]*)\\/'<br><br>What I get is:<br><br><div style="margin-left: 40px;">referrer <br>substring<br>substring_1<br>substring_2<br><br><a href="http://www.google.ca/search?q=one+hour+heating&amp;hl=en">
http://www.google.ca/search?q=one+hour+heating&amp;hl=en</a><br><a href="http://www.google.ca">http://www.google.ca</a><br>q=one+hour+heating&amp;hl=en<br>q=<br><br><a href="http://www.google.com/search?hl=en&amp;q=One+hour+heating+and+Air">
http://www.google.com/search?hl=en&amp;q=One+hour+heating+and+Air</a><br><a href="http://www.google.com">http://www.google.com</a><br>hl=en&amp;q=One+hour+heating+and+Air<br>hl=en&amp;q=<br></div><br>What I expected for substring_2 was (respectively):
<br><br><div style="margin-left: 40px;">one+hour+heating<br></div><div style="margin-left: 40px;"><br></div><div style="margin-left: 40px;">One+hour+heating+and+Air<br></div><br>I thought by using the FOR '#' I could specify exactly what part of the expression I would get but it still grabs the first (...) of the pattern.&nbsp; At least that is what the documentation in seciton 
9.7.2 at <a href="http://www.postgresql.org/docs/8.1/static/functions-matching.html">http://www.postgresql.org/docs/8.1/static/functions-matching.html</a> led me to believe.&nbsp; How can I get the part of the string I am really after without using one nested substring after another?
<br><br>Thanks,<br>Aaron Bono<br><br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com
</a><br>==================================================================

------=_Part_42718_7764933.1153001003408--