Re: SELECT substring with regex

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