X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 28E739FB1ED
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 16:45:57 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 61578-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  7 Jul 2006 16:45:54 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.188])
	by postgresql.org (Postfix) with ESMTP id 001239FB1DB
	for <pgsql-sql@postgresql.org>; Fri,  7 Jul 2006 16:45:53 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id a4so85190nfc
	for <pgsql-sql@postgresql.org>; Fri, 07 Jul 2006 12:45:52 -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:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=UpzW701bZ09chh3WEngdTE5FNvsTLQLQhZ9jW7JZRtdlpiZEGopvNwnh4b0va4J0VxlVhbxJuW7OAJ+HvPhaQtmvMATaCgdGRw4OUvQxew7uI3tWz93FmS4/ejOLxNCzjtaTLiUBxsQxmHRTlZosfLirgt4EIxFsXIoOvy4opN8=
Received: by 10.78.177.3 with SMTP id z3mr861258hue;
	Fri, 07 Jul 2006 12:45:52 -0700 (PDT)
Received: by 10.78.29.2 with HTTP; Fri, 7 Jul 2006 12:45:51 -0700 (PDT)
Message-ID: <bf05e51c0607071245w3738e109l8128d345f5b06977@mail.gmail.com>
Date: Fri, 7 Jul 2006 14:45:51 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Rodrigo De Leon" <rdeleonp@gmail.com>
Subject: Re: SELECT substring with regex
Cc: pgsql-sql@postgresql.org
In-Reply-To: <a55915760607071137q2c70346cp5b30cac14beaf1e7@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_4844_10301033.1152301551938"
References: <44AE66E0.1040606@numerixtechnology.de>
	<200607071659.14940.gary.stainburn@ringways.co.uk>
	<44AE9B4F.1020401@numerixtechnology.de>
	<a55915760607071137q2c70346cp5b30cac14beaf1e7@mail.gmail.com>
X-Google-Sender-Auth: 6b387a9d58389299
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/116
X-Sequence-Number: 25690

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

On 7/7/06, Rodrigo De Leon <rdeleonp@gmail.com> wrote:
>
> On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> wrote:
> > But that takes me to the next problem:
> >
> > For the sake of the example I simplified the regular pattern.
> > In reality, BASE_NAME might be:
> >
> > 28mm
> > 28-70mm
> >
> > So the reg. expr. requires brackets:
> >
> > substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME
> >
> > Actually, the pattern is more complex than that and I cannot see how I
> > can express it without brackets.
>
> Maybe:
>
> select
> substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> , substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
>
> select
> substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
> , substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;
>
> etc...
>
> Regards,
>
> Rodrigo


Is there a reason this column wasn't separated into two different columns?
Or perhaps into a child table if there could be more than one XXXmm value in
the field?

Just curious.

-Aaron

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

On 7/7/06, <b class="gmail_sendername">Rodrigo De Leon</b> &lt;<a href="mailto:rdeleonp@gmail.com">rdeleonp@gmail.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On 7/7/06, T E Schmitz &lt;<a href="mailto:mailreg@numerixtechnology.de">mailreg@numerixtechnology.de</a>&gt; wrote:<br>&gt; But that takes me to the next problem:<br>&gt;<br>&gt; For the sake of the example I simplified the regular pattern.
<br>&gt; In reality, BASE_NAME might be:<br>&gt;<br>&gt; 28mm<br>&gt; 28-70mm<br>&gt;<br>&gt; So the reg. expr. requires brackets:<br>&gt;<br>&gt; substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME<br>&gt;<br>&gt; Actually, the pattern is more complex than that and I cannot see how I
<br>&gt; can express it without brackets.<br><br>Maybe:<br><br>select<br>substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME<br>, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;<br><br>select
<br>substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME<br>, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;<br><br>etc...<br><br>Regards,<br><br>Rodrigo</blockquote><div><br>Is there a reason this column wasn't separated into two different columns?&nbsp; Or perhaps into a child table if there could be more than one XXXmm value in the field?
<br><br>Just curious.<br><br>-Aaron<br></div><br></div>

------=_Part_4844_10301033.1152301551938--