DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=KjTjazH5DTXXkURSH0bjp6ITfOkWe+4I+mWkIQy8MC5TW/QJpAH5/s41wKNUSj17RrxutWfxf4D2fnuUnUjcfUbgyFqEiHpt/Rrw2+8MbzNe7q6q5DN/2QPb3JUm/0KnQeogPvrxuvgGAQHVNUl2+D4UVyMdX1AolPcL7O0MBxc=
Message-ID: <a55915760607070855j57b6b243lf1fd81dce1036bed@mail.gmail.com>
Date: Fri, 7 Jul 2006 10:55:17 -0500
From: "Rodrigo De Leon" <rdeleonp@gmail.com>
Reply-To: rdeleonp@gmail.com, rdeleonp@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: SELECT substring with regex
In-Reply-To: <a55915760607070851s2e1e36a1t6341c285a87061f3@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <44AE66E0.1040606@numerixtechnology.de>
	<a55915760607070851s2e1e36a1t6341c285a87061f3@mail.gmail.com>

On 7/7/06, T E Schmitz <mailreg@numerixtechnology.de> 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?
>
> --
>
>
> Regards,
>
> Tarlika Elisabeth Schmitz

I'm sure there's a cleaner, regexp based approach, but how about:

SELECT
substring (NAME, '^\\d+mm') AS BASE_NAME ,
substr(
        NAME
        , char_length(
                substring (NAME, '^\\d+mm')
        ) + 2
) AS SUFFIX
FROM MODEL

Regards,

Rodrigo