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 BAC059FB227 for ; Fri, 7 Jul 2006 19:39:52 -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 22970-07 for ; Fri, 7 Jul 2006 19:39:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id 253449FB1DA for ; Fri, 7 Jul 2006 19:39:48 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id l37so31948nfc for ; Fri, 07 Jul 2006 15:39:47 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=tYHfuUP2vC7tS26nkeJpC9ygkk/U9oLC9eErf7Q1dsLE4NRSBKMcXKYCiE8GOalbjFX5B0JgKVhOymE7sW6g/g+abcMxGNzwN37f93Em6xFQRqfUqsCGqJ5aVi8bl/NjKL32o8zGFzZwtc6rW89OPDy4mggrzL8Lowks+ZXhZeU= Received: by 10.78.140.17 with SMTP id n17mr909857hud; Fri, 07 Jul 2006 15:39:47 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Fri, 7 Jul 2006 15:39:47 -0700 (PDT) Message-ID: Date: Fri, 7 Jul 2006 17:39:47 -0500 From: "Aaron Bono" To: mailreg@numerixtechnology.de Subject: Re: SELECT substring with regex Cc: pgsql-sql@postgresql.org In-Reply-To: <44AEC8C5.80103@numerixtechnology.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7051_22145543.1152311987453" References: <44AE66E0.1040606@numerixtechnology.de> <200607071659.14940.gary.stainburn@ringways.co.uk> <44AE9B4F.1020401@numerixtechnology.de> <44AEAED9.7080300@myemma.com> <44AEC8C5.80103@numerixtechnology.de> X-Google-Sender-Auth: 325d75623d719bbb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/123 X-Sequence-Number: 25697 ------=_Part_7051_22145543.1152311987453 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, T E Schmitz wrote: > > > I am trying to come up with a semi-automatic solution to tidy up some > data. If it's got to be done manually via the GUI it would mean a lot of > dummy work [for the customer]. I would recommend you alter the GUI to NOT allow any more bad data to get in - perhaps give some nice validation and friendly error message about the correct format or give various fields that need to be filled out so the user can easily enter it and the GUI assembles the correct string. It won't fix the current bad data but would stop the insanity from proliferating :)- First of all I did a 5 table join to select those NAMEs which don't > follow the required pattern: the pattern describes a photographic lens > (focal length followed by lens speed (aperture)) and nothing else. > Unfortuantely, there are a few hundred occurences where a few attributes > have been appended which should have been stored elsewhere. > > > 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) > > > In the WHERE clause I have specified all those NAMEs, which follow that > pattern but have some gubbins appended: > > WHERE NAME ~ > '^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$' > > > which gives me a listing of those candidates that need to be amended - > manually or otherwise. > > Next, I wanted to produce a result which splits NAME into what it should > be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from > that to tidy up the data. You might also consider adding the base and suffix columns with a trigger that parses the name field and sets the values of base and suffix and also does any integrity checks during inserts and updates. Finally, after the data is cleaned up and the client apps are changed to use base and suffix and not name, get rid of the name column. Then again, this may be exactly what you are already trying to do. -Aaron ------=_Part_7051_22145543.1152311987453 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> wrote:

I am trying to come up with a semi-automatic solution to tidy up some
data. If it's got to be done manually via the GUI it would mean a lot of
dummy work [for the customer].

I would recommend you alter the GUI to NOT allow any more bad data to get in - perhaps give some nice validation and friendly error message about the correct format or give various fields that need to be filled out so the user can easily enter it and the GUI assembles the correct string.  It won't fix the current bad data but would stop the insanity from proliferating :)-

First of all I did a 5 table join to select those NAMEs which don't
follow the required pattern: the pattern describes a photographic lens
(focal length followed by lens speed (aperture)) and nothing else.
Unfortuantely, there are a few hundred occurences where a few attributes
have been appended which should have been stored elsewhere.


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)


In the WHERE clause I have specified all those NAMEs, which follow that
pattern but have some gubbins appended:

WHERE NAME ~
'^((\\d+(-\\d+)?)mm F((\\d+(\.\\d+)?)+(\.(\\d+(\.\\d+)?)+)?))\\D+$'


which gives me a listing of those candidates that need to be amended -
manually or otherwise.

Next, I wanted to produce a result which splits NAME into what it should
be (BASE) and attributes (SUFFIX). Maybe I can generate some SQL from
that to tidy up the data.

You might also consider adding the base and suffix columns with a trigger that parses the name field and sets the values of base and suffix and also does any integrity checks during inserts and updates.  Finally, after the data is cleaned up and the client apps are changed to use base and suffix and not name, get rid of the name column.

Then again, this may be exactly what you are already trying to do.

-Aaron

------=_Part_7051_22145543.1152311987453--