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 0393D9FB1CC for ; Fri, 7 Jul 2006 22:24:14 -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 16252-10 for ; Fri, 7 Jul 2006 22:24:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 718029FB1C6 for ; Fri, 7 Jul 2006 22:24:09 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so3306485ugf for ; Fri, 07 Jul 2006 18:24:07 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=QIi3NBjvUZ/jfwPTFH8wuF6GVi9edy5qlXdD5LSJKBJ8+wRE/pq2ADfOlUN5Q52cRCoct1dc9tjrBoBIO6oIH5viKu3Q65u3t0Kt5brO5wYJ9DCx49WeMCXZ3+WuWBUvsIq8kxeeLKTlkTk8vqaYTuiBwAk/6/hPfivp1gpDU9I= Received: by 10.67.29.12 with SMTP id g12mr2570027ugj; Fri, 07 Jul 2006 18:24:07 -0700 (PDT) Received: by 10.67.24.20 with HTTP; Fri, 7 Jul 2006 18:24:07 -0700 (PDT) Message-ID: Date: Fri, 7 Jul 2006 20:24:07 -0500 From: "Rodrigo De Leon" To: pgsql-sql@postgresql.org Subject: Re: SELECT substring with regex In-Reply-To: <44AEB4C6.9000903@numerixtechnology.de> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <44AE66E0.1040606@numerixtechnology.de> <200607071659.14940.gary.stainburn@ringways.co.uk> <44AE9B4F.1020401@numerixtechnology.de> <44AEB4C6.9000903@numerixtechnology.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/124 X-Sequence-Number: 25698 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 Regards, Rodrigo