pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPlpgsql: Assign regular expression match to variable
3+ messages / 2 participants
[nested] [flat]
* Plpgsql: Assign regular expression match to variable
@ 2009-09-01 12:01 Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 3+ messages in thread
From: Leif B. Kristensen @ 2009-09-01 12:01 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Plpgsql: Assign regular expression match to variable
@ 2009-09-01 12:36 Ian Barwick <barwick@gmail.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 3+ messages in thread
From: Ian Barwick @ 2009-09-01 12:36 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Plpgsql: Assign regular expression match to variable
@ 2009-09-01 12:56 Leif B. Kristensen <leif@solumslekt.org>
parent: Ian Barwick <barwick@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Leif B. Kristensen @ 2009-09-01 12:56 UTC (permalink / raw)
To: Ian Barwick <barwick@gmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-09-01 12:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-01 12:01 Plpgsql: Assign regular expression match to variable Leif B. Kristensen <leif@solumslekt.org>
2009-09-01 12:36 ` Ian Barwick <barwick@gmail.com>
2009-09-01 12:56 ` Leif B. Kristensen <leif@solumslekt.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox