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 DF6AD9FB2A2 for ; Mon, 10 Jul 2006 08:08:15 -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 81821-03 for ; Mon, 10 Jul 2006 08:08:10 -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 1ADA79FB261 for ; Mon, 10 Jul 2006 08:08:09 -0300 (ADT) Received: from host217-32-131-234.webport.bt.net ([217.32.131.234] helo=bolek.coachhouse) by dougal.cranntara.net with asmtp (Exim 3.36 #1 (Debian)) id 1Fztdx-0000M3-00; Mon, 10 Jul 2006 12:09:38 +0100 Received: (Exim 3.36) #1 (Debian)) protocol: esmtp id 1FztWh-0008Jp-00 ; Mon, 10 Jul 2006 12:02:07 +0100 Message-ID: <44B233AE.7080003@numerixtechnology.de> Date: Mon, 10 Jul 2006 12:02:06 +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: emils.klotins@gmail.com, 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> <44AEAED9.7080300@myemma.com> <44AEC8C5.80103@numerixtechnology.de> <9dcb6fa40607092211g5e0e2099x3e6d9d3c56977a02@mail.gmail.com> In-Reply-To: <9dcb6fa40607092211g5e0e2099x3e6d9d3c56977a02@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/144 X-Sequence-Number: 25718 Emils wrote: > 2006/7/7, T E Schmitz : > >> >> valid entries would be: >> "28mm F2.8" (prime lens) >> "30-70mm F4" (zoom lens) >> "30-70mm F2.8" (zoom lens) >> "30-100mm F4.5-5.6" (zoom lens with variable speed) > > > If these are the cases, wouldn't the regex be simply: > > "^[\d\-]+mm" for BASE > > "^[\d\-]+mm (.+)$" for SUFFIX > > Or are you having to deal with malformatted data too (like "30 -70mm" > or "28 mm ")? There were quite a few malformed MODEL.NAMEs (upper/lower case, additional blanks, missing mm) and therefore a precise regexp was required. I did this as a 2-stage process: WHERE !~ '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))$' selected all malformed MODEL.NAMEs I corrected the malformed basenames, which left me with the BASE/SUFFIX problem only. I selected all of those and /knowing/ that none of them were malformed, I used a simplified pattern without round brackets to produce the BASE and SUFFIX result columns: substring (MODEL.MODEL_NAME, '^[-\\d]+mm F[-\\d\.]+' ) as BASE, substring (MODEL.MODEL_NAME, '^[-\\d]+mm F[-\\d\.]+ *(.*)$') as SUFFIX exported that together with PKs as CSV and generated SQL from it. Basically, my problem had been that I needed to express the SUFFIX pattern without using brackets for the BASE. I would like to thank everyone who contributed to this thread. -- Regards/Gruß, Tarlika Elisabeth Schmitz