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 0C19B9FB20C for ; Fri, 7 Jul 2006 17:23:27 -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 12693-10 for ; Fri, 7 Jul 2006 17:23:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 376599FB1EC for ; Fri, 7 Jul 2006 17:23:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from dougal.cranntara.net (unknown [217.112.90.247]) by svr4.postgresql.org (Postfix) with ESMTP id 3CD265AF065 for ; Fri, 7 Jul 2006 20:23:22 +0000 (GMT) Received: from host217-32-120-150.webport.bt.net ([217.32.120.150] helo=bolek.coachhouse) by dougal.cranntara.net with asmtp (Exim 3.36 #1 (Debian)) id 1Fywie-0003iJ-00; Fri, 07 Jul 2006 21:14:33 +0100 Received: (Exim 3.36) #1 (Debian)) protocol: esmtp id 1Fywh3-0005IU-00 ; Fri, 07 Jul 2006 21:12:53 +0100 Message-ID: <44AEC044.1040206@numerixtechnology.de> Date: Fri, 07 Jul 2006 21:12:52 +0100 From: T E Schmitz Reply-To: mailreg@numerixtechnology.de User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Aaron Bono , pgsql-sql@postgresql.org Subject: Re: SELECT substring with regex References: <44AE66E0.1040606@numerixtechnology.de> <200607071659.14940.gary.stainburn@ringways.co.uk> <44AE9B4F.1020401@numerixtechnology.de> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/117 X-Sequence-Number: 25691 Aaron Bono wrote: > On 7/7/06, *Rodrigo De Leon* > wrote: > > On 7/7/06, T E Schmitz > 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. You're absolutely right (see my other posting): what was entered: MODEL.NAME "150mm F4 E" TYPE.NAME - MODEL.NAME "150mm F4 PE" TYPE.NAME - what should've been entered: MODEL.NAME "150mm F4" TYPE.NAME "PE" TYPE.NAME "E" both referencing the same MODEL -- Regards, Tarlika Elisabeth Schmitz