From: T E Schmitz <mailreg@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: SELECT substring with regex
Date: Fri, 07 Jul 2006 18:35:11 +0100
Message-ID: <44AE9B4F.1020401@numerixtechnology.de> (raw)
In-Reply-To: <200607071659.14940.gary.stainburn@ringways.co.uk>
References: <44AE66E0.1040606@numerixtechnology.de>
<200607071659.14940.gary.stainburn@ringways.co.uk>
Gary Stainburn wrote:
> 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:
>>
>>The column contains something like
>>"150mm LD AD Asp XR Macro"
>>I want to split this into
>>"150mm", "LD AD Asp XR Macro"
>>
>
>
> 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
This is ingenious! I had been looking at chapter 9.6 Pattern Matching.
Am I missing something? I did not realize that the brackets indicate
the required match.
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.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de
Subject: Re: SELECT substring with regex
In-Reply-To: <44AE9B4F.1020401@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox