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

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