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 F30159FB1E1 for ; Sat, 8 Jul 2006 06:55:13 -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 24758-04 for ; Sat, 8 Jul 2006 06:55:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from dougal.cranntara.net (unknown [217.112.91.201]) by postgresql.org (Postfix) with ESMTP id 65AB29FA4A8 for ; Sat, 8 Jul 2006 06:53:12 -0300 (ADT) Received: from host217-32-121-89.webport.bt.net ([217.32.121.89] helo=bolek.coachhouse) by dougal.cranntara.net with asmtp (Exim 3.36 #1 (Debian)) id 1Fz9WC-0004Iz-00; Sat, 08 Jul 2006 10:54:33 +0100 Received: (Exim 3.36) #1 (Debian)) protocol: esmtp id 1Fz9IU-0005fo-00 ; Sat, 08 Jul 2006 10:40:22 +0100 Message-ID: <44AF7D78.2050007@numerixtechnology.de> Date: Sat, 08 Jul 2006 10:40:08 +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: Rodrigo De Leon CC: 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> <44AEB4C6.9000903@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/129 X-Sequence-Number: 25703 Rodrigo De Leon wrote: > On 7/7/06, T E Schmitz wrote: > >> 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. >> >> ... >> >> Still, I'd be interested to know whether there is a 'more elegant' >> solution. > > > OK, last try using regex: > > SELECT > NAME > , substring(NAME, '^(\\d+(-\\d+)?mm)') AS BASE_NAME > , regexp_replace(NAME, '^\\d+(-\\d+)?mm (.*)', '\\2') AS SUFFIX > FROM MODEL Is regexp_replace a new feature? I am running v 7.4. -- Regards, Tarlika