Re: SELECT substring with regex

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