From: T E Schmitz <mailreg@numerixtechnology.de>
To: emils.klotins@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: SELECT substring with regex
Date: Mon, 10 Jul 2006 12:02:06 +0100
Message-ID: <44B233AE.7080003@numerixtechnology.de> (raw)
In-Reply-To: <9dcb6fa40607092211g5e0e2099x3e6d9d3c56977a02@mail.gmail.com>
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>
Emils wrote:
> 2006/7/7, T E Schmitz <mailreg@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)
>
>
> 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
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: mailreg@numerixtechnology.de, emils.klotins@gmail.com
Subject: Re: SELECT substring with regex
In-Reply-To: <44B233AE.7080003@numerixtechnology.de>
* 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