Re: Plpgsql: Assign regular expression match to variable

2009/9/1, Leif B. Kristensen <leif(at)solumslekt(dot)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