Thread: Plpgsql: Assign regular expression match to variable

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.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

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

On Tuesday 1. September 2009, Ian Barwick wrote:

>This seems to do what you want:
>
>  my_int := (REGEXP_MATCHES(txt, E'^#(\\d+)'))[1];

Great! I had no idea that REGEXP_MATCHES() could do that kind of stuff.

pgslekt=> select (REGEXP_MATCHES('#42 blabla', E'^#(\\d+)'))
[1]::integer;regexp_matches
----------------            42
(1 row)

Thank you very much.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/