From: Richard Broersma Jr <rabroersma@yahoo.com>
To: mailreg@numerixtechnology.de
To: pgsql-sql@postgresql.org
Subject: Re: SELECT substring with regex
Date: Fri, 7 Jul 2006 14:33:01 -0700 (PDT)
Message-ID: <20060707213301.36130.qmail@web31803.mail.mud.yahoo.com> (raw)
In-Reply-To: <44AEC8C5.80103@numerixtechnology.de>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, mailreg@numerixtechnology.de
Subject: Re: SELECT substring with regex
In-Reply-To: <20060707213301.36130.qmail@web31803.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox