Re: SELECT substring with regex

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: SELECT substring with regex
Date: 2006-07-07 15:59:14
Message-ID: 200607071659.14940.gary.stainburn@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma Jr 2006-07-07 15:59:24 Re: create aggregate function 'count_bool( column_name, boolean )'
Previous Message Rodrigo De Leon 2006-07-07 15:55:17 Re: SELECT substring with regex