Re: SELECT substring with regex

On 7/7/06, T E Schmitz  wrote:
> But that takes me to the next problem:
>
> For the sake of the example I simplified the regular pattern.
> In reality, BASE_NAME might be:
>
> 28mm
> 28-70mm
>
> So the reg. expr. requires brackets:
>
> substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME
>
> Actually, the pattern is more complex than that and I cannot see how I
> can express it without brackets.

Maybe:

select
substring ('150mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('150mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;

select
substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
, substring('28-70mm LD AD Asp XR Macro','^[\\d-]*mm (.*)$') as SUFFIX;

etc...

Regards,

Rodrigo