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 6F4419FB1EC for ; Fri, 7 Jul 2006 17:34:40 -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 34745-10 for ; Fri, 7 Jul 2006 17:34:36 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from amanda.contactbda.com (ipn36372-e65122.cidr.lightship.net [216.204.66.226]) by postgresql.org (Postfix) with ESMTP id 7C23D9FB1E9 for ; Fri, 7 Jul 2006 17:34:36 -0300 (ADT) Received: from amanda.contactbda.com (amanda.contactbda.com [192.168.1.2]) by amanda.contactbda.com (8.12.11/8.12.11/Debian-3) with ESMTP id k67KYWqD032601; Fri, 7 Jul 2006 16:34:32 -0400 From: "Jim Buttafuoco" To: mailreg@numerixtechnology.de, pgsql-sql@postgresql.org Reply-To: jim@contactbda.com Subject: Re: SELECT substring with regex Date: Fri, 7 Jul 2006 16:34:32 -0400 Message-Id: <20060707203420.M77919@contactbda.com> In-Reply-To: <44AEB4C6.9000903@numerixtechnology.de> References: <44AE66E0.1040606@numerixtechnology.de> <200607071659.14940.gary.stainburn@ringways.co.uk> <44AE9B4F.1020401@numerixtechnology.de> <44AEB4C6.9000903@numerixtechnology.de> X-Mailer: Open WebMail 2.41 20040926 X-OriginatingIP: 68.171.253.205 (jim) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/118 X-Sequence-Number: 25692 use plperl ---------- Original Message ----------- From: T E Schmitz To: pgsql-sql@postgresql.org Sent: Fri, 07 Jul 2006 20:23:50 +0100 Subject: Re: [SQL] SELECT substring with regex > 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 ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME > > Sorry, but that would also capture something like > 10-30-59mm > > The pattern describes either a single length (120 millimeters) or a > range (30 to 70 millimetres), hence: > > \\d+(-\\d+)?mm > > The ? quantifier refers to the combination of '-' and digits and has to > be bracketed. > > If the brackets cannot be avoided in the expression, your original > suggestion might come in handy though: > > SELECT > substring (NAME, '^\\d+(-\\d+)?mm') AS BASE_NAME , > substr( > NAME > , char_length( > substring (NAME, '^\\d+(-\\d+)?mm') > ) + 2 > ) AS SUFFIX > > Still, I'd be interested to know whether there is a 'more elegant' solution. > > -- > > Regards, > > Tarlika Elisabeth Schmitz > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org ------- End of Original Message -------