Re: SELECT substring with regex - Mailing list pgsql-sql

On Friday 07 July 2006 14:51, 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?

select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,      substring('150mm LD AD Asp XR Macro','^\\d+mm
(.*)$')as SUFFIX;base_name |       suffix
 
-----------+--------------------150mm     | LD AD Asp XR Macro
(1 row)

The brackets surround the required match
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000