From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: SELECT substring with regex
Date: Fri, 7 Jul 2006 16:59:14 +0100
User-Agent: KMail/1.9.1
References: <44AE66E0.1040606@numerixtechnology.de>
In-Reply-To: <44AE66E0.1040606@numerixtechnology.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200607071659.14940.gary.stainburn@ringways.co.uk>

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