Message-ID: <44AE9B4F.1020401@numerixtechnology.de>
Date: Fri, 07 Jul 2006 18:35:11 +0100
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: SELECT substring with regex
References: <44AE66E0.1040606@numerixtechnology.de>
	<200607071659.14940.gary.stainburn@ringways.co.uk>
In-Reply-To: <200607071659.14940.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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