DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=kJ4w1w8cKromtO/95hAtuNcKDkoVHLN9RtSD7pYyTgkD2kmqBQXD1SZy15yiu42S2h
	VqHnClFOjqQ/hoXpnuezSyMzXBXUEg++3hkSyU6rm3tHAKMB26DqTi2+NvYnDP7HQKp6
	2aHdkJbaQoWQfdiH9GW+FgfZIcwrvb2B2A69A=
MIME-Version: 1.0
In-Reply-To: <200909011401.00231.leif@solumslekt.org>
References: <200909011401.00231.leif@solumslekt.org>
Date: Tue, 1 Sep 2009 21:36:18 +0900
Message-ID: <1d581afe0909010536g6fdb57a0u87c8d748fb9067b6@mail.gmail.com>
Subject: Re: Plpgsql: Assign regular expression match to variable
From: Ian Barwick <barwick@gmail.com>
To: "Leif B. Kristensen" <leif@solumslekt.org>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8

2009/9/1, Leif B. Kristensen <leif@solumslekt.org>:
> In Plpgsql, I've got this problem of how to assign an integer extracted
> from a regex to a variable. My approach so far feels kludgy:
>
> -- extract ^#(\d+) from txt
> IF txt SIMILAR TO E'#\\d+%' THEN
>     my_int := SUBSTR(SUBSTRING(txt, E'#\\d+'), 2,
>         LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER;
>     -- strip ^#\d+ from text
>     my_txt := REGEXP_REPLACE(txt, E'^#\\d+ ', '');
> END IF;
>
> What I'd like to do is something like this:
>
> my_int := MATCH(txt, '^#(\d+)')::INTEGER;
>
> which would assign the integer atom (\d+) to my_int.

This seems to do what you want:

  my_int := (REGEXP_MATCHES(txt, E'^#(\\d+)'))[1];


Ian Barwick