[SQL] 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/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Plpgsql: Assign regular expression match to variable
2009/9/1, Leif B. Kristensen : > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Plpgsql: Assign regular expression match to variable
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/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Odd sort behaviour
Since when does "." sort as "nothing at all" This select select distinct u.user_name from subscriber_user u, subscription s, subscription_template t where u.id = s.subscriber_entity_id and s.template_id = t.id and t.application_id = (select id from application where short_name ='books') order by u.user_name \p\g over this table def.(for user_name) \d subscriber_user Table "public.subscriber_user" +---+-+---+ |Column | Type | Modifiers | +---+-+---+ | id| bigint | not null | | last_name | text| | | first_name| text| | | user_name | text| | | email_address | text| | | force_password_change | boolean | default false | | title | text| | +---+-+---+ Indexes: "subscriber_user_pkey" PRIMARY KEY, btree (id) "idx__subscriber_users__lower_email_address" UNIQUE, btree (lower(email_address)) "idx__subscriber_users__lower_user_name" UNIQUE, btree (lower(user_name)) Foreign-key constraints: "subscriber_user_id_fkey" FOREIGN KEY (id) REFERENCES subscriber(id) is producing this sorted??? output | adrianoha...@hotmail.com | | adx...@show.org.tw | | aeche...@mac.com | | a.eck...@gmx.de| | aelef...@unina.it | | aeo...@hotmail.com | | a.fisched...@t-online.de | | aflores3...@gmail.com | | afr...@advancedneurosurgeons.com | | agave...@comcast.net | | agelsin...@amirsys.com | | agis1...@yahoo.gr | using this client Welcome to psql 8.3.7, the PostgreSQL interactive terminal. and this server show server_version; ++ | server_version | ++ | 8.3.7 | ++ (1 row) nsm=# show server_encoding +-+ | server_encoding | +-+ | UTF8| +-+ (1 row) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Odd sort behaviour
On Tue, Sep 1, 2009 at 3:01 PM, Rob Sargent wrote: > Since when does "." sort as "nothing at all" Since you set your locale equal to something like en_US instead of C -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Odd sort behaviour
How many ways might one accidentally do that I wonder. Scott Marlowe wrote: On Tue, Sep 1, 2009 at 3:01 PM, Rob Sargent wrote: Since when does "." sort as "nothing at all" Since you set your locale equal to something like en_US instead of C -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Odd sort behaviour
On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent wrote: > How many ways might one accidentally do that I wonder. Well most operating system distributions ask you when you install them what region you're in and use a collation for that region. In 8.4 you can check what collation a database is set to use with \l in psql. In 8.3 the entire "cluster" has a single collation which you can see using "show lc_collate". You can see how your system's collations work by running sort: $ LC_ALL=c sort s a.eck...@gmx.de a.fisched...@t-online.de adrianoha...@hotmail.com adx...@show.org.tw aeche...@mac.com aelef...@unina.it aeo...@hotmail.com aflores3...@gmail.com afr...@advancedneurosurgeons.com agave...@comcast.net agelsin...@amirsys.com agis1...@yahoo.gr $ LC_ALL=en_US sort s adrianoha...@hotmail.com adx...@show.org.tw aeche...@mac.com a.eck...@gmx.de aelef...@unina.it aeo...@hotmail.com a.fisched...@t-online.de aflores3...@gmail.com afr...@advancedneurosurgeons.com agave...@comcast.net agelsin...@amirsys.com agis1...@yahoo.gr -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Odd sort behaviour
OK, I'm waking up now. My locale is as Scott suspected, en-US.UTF-8, and of course my server too. I guess I never really left "C" intellectually :) and we have a server that thinks SQL-ASCII is cool and comparing lists of names and emails between that server and my local utf-8 one was rather perplexing. I'm sure this a life-time's worth of discussion on the merits of treating "." as nothing when sorting Sorry for the noise. Greg Stark wrote: On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent wrote: How many ways might one accidentally do that I wonder. Well most operating system distributions ask you when you install them what region you're in and use a collation for that region. In 8.4 you can check what collation a database is set to use with \l in psql. In 8.3 the entire "cluster" has a single collation which you can see using "show lc_collate". You can see how your system's collations work by running sort: $ LC_ALL=c sort s a.eck...@gmx.de a.fisched...@t-online.de adrianoha...@hotmail.com adx...@show.org.tw aeche...@mac.com aelef...@unina.it aeo...@hotmail.com aflores3...@gmail.com afr...@advancedneurosurgeons.com agave...@comcast.net agelsin...@amirsys.com agis1...@yahoo.gr $ LC_ALL=en_US sort s adrianoha...@hotmail.com adx...@show.org.tw aeche...@mac.com a.eck...@gmx.de aelef...@unina.it aeo...@hotmail.com a.fisched...@t-online.de aflores3...@gmail.com afr...@advancedneurosurgeons.com agave...@comcast.net agelsin...@amirsys.com agis1...@yahoo.gr -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
