Re: SELECT substring with regex

On 7/7/06, T E Schmitz  wrote:
> I would like to split the contents of a column using substring with a
> regular expression:
>
> SELECT
>   substring (NAME, '^\\d+mm') as BASE_NAME,
>   substring (NAME, ??? ) as SUFFIX
> FROM MODEL
>
> The column contains something like
> "150mm LD AD Asp XR Macro"
> I want to split this into
> "150mm", "LD AD Asp XR Macro"
>
> How can I extract the bit following the matching substring?
>
> --
>
>
> Regards,
>
> Tarlika Elisabeth Schmitz

I'm sure there's a cleaner, regexp based approach, but how about:

SELECT
substring (NAME, '^\\d+mm') AS BASE_NAME ,
substr(       NAME       , char_length(               substring (NAME, '^\\d+mm')       ) + 2
) AS SUFFIX
FROM MODEL

Regards,

Rodrigo