DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=JlQXzr67DPH6qwUHPWqtnYVTfU3ZRgtZFNdt9bEla485gi2I9ZLNoyrpL2Uo779SCkRpcvkzFoSMJhdYYUpgQuZqUC+5zLWjltXC66iVMNbmhXZoEeHhbTum7d2IXuct0Vw5Fx3H8aEWEBMhQ00nVzozYCPsvTmV/AR3dJxQ7BU=
	;
Message-ID: <20060707213301.36130.qmail@web31803.mail.mud.yahoo.com>
Date: Fri, 7 Jul 2006 14:33:01 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: SELECT substring with regex
To: mailreg@numerixtechnology.de, pgsql-sql@postgresql.org
In-Reply-To: <44AEC8C5.80103@numerixtechnology.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


> 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.

would this give you the results you want?  It admit that it doesn't look to elegant.

name:
substr(your_string, 0, strpos(your_string, ' ')+1)

suffix:
substr(your_string, length(your_string)-strpos(your_string, ' '), length(your_string))

Regards,

Richard Broersma Jr.