Re: Plpgsql: Assign regular expression match to variable - Mailing list pgsql-sql

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