Re: [SQL] window function to sort times series data?
In going through the arcana of string functions, I have come across the
following series of selects that contain, for me, a mysterious "$re$".
-- return all matches from regexp
SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$);
-- test case insensitive
SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i');
-- global option - more than one match
SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$,
'g');
When I run this I get:
regexp_matches
--
I have not been able to find out what it all means. Forgive me for my
blindness.
John
P.S. The author of the statements is "alexk" at Command Prompt. They are
test statements against Postgres' string functions.
[SQL] Help with reg_exp
[I mistakenly used the wrong Subject line initially with this post]
In going through the arcana of string functions, I have come across the
following series of selects that contain, for me, a mysterious "$re$".
-- return all matches from regexp
SELECT regexp_matches('
foobarbequebaz', $re$(bar)(beque)$re$);
-- test case insensitive
SELECT regexp_matches('foObARbEqUEbAz', $re$(bar)(beque)$re$, 'i');
-- global option - more than one match
SELECT regexp_matches('foobarbequebazilbarfbonk', $re$(b[^b]+)(b[^b]+)$re$,
'g');
When I run this I get:
regexp_matches
--
I have not been able to find out what it all means. Forgive me for my
blindness.
John
P.S. The author of the statements is "alexk" at Command Prompt. They are
test statements against Postgres' string functions.
[SQL] Dollar quoted strings
Thanks very much for this. I am using 8.4.2.
This query works as you describe in pgAdmin.
I had tried it in the SquirrelSQL client, which is where it produced the
result.
Obviously, this is a SquirrelSQL problem at least in part.
What does mean?
Thanks again for directing me to the doc's on dollar quoting.
John
On Mar 24, 2010, at 8:13 PM, Josh Kupershmidt wrote:
On Wed, Mar 24, 2010 at 2:38 PM, John Gage wrote:
In going through the arcana of string functions, I have come across
the
following series of selects that contain, for me, a mysterious "$re
$".
-- return all matches from regexp
SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$);
The $re$ is just an arbitrary identifier for a dollar-quoted string
constant. See:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html
Maybe you're on an old version which doesn't support dollar-quoted
strings?
On 8.3 for the above query, I get:
SELECT regexp_matches('foobarbequebaz', $re$(bar)(beque)$re$);
regexp_matches
{bar,beque}
(1 row)
Josh
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Tsearch not searching 'Y'
You can avoid stemming by using 'simple' instead of 'english' as the
language of the words in to_tsvector (which is a little more awkward
than the cast).
"There are no stop words for the simple dictionary. It will just
convert to lower case, and index every unique word.
SELECT to_tsvector('simple', 'Andy andy The the in out');
to_tsvector
-
'in':5 'out':6 'the':3,4 'andy':1,2
(1 row)
John
On Apr 29, 2010, at 4:01 PM, Tom Lane wrote:
"sandeep prakash dhumale" writes:
I am trying to get tsearch working for my application but I am
facing a
problem when alphabet 'Y' is the in the tsquery.
# SELECT 'hollywood'::tsvector @@ to_tsquery('holly:*');
?column?
--
f
(1 row)
You can't use to_tsquery for this sort of thing, because it tries to
normalize the given words:
regression=# select to_tsquery('holly:*');
to_tsquery
'holli':*
(1 row)
If you do this it works:
regression=# SELECT 'hollywood'::tsvector @@ 'holly:*'::tsquery;
?column?
--
t
(1 row)
So if you want to use prefix matching, don't normalize.
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How do you do the opposite of regexp_split_to_table?
I would like to take several rows from a select and have them grouped into one row with the text contents of one field in all the rows concatenated into one field in the single row of the group group. The contents of the other fields are identical across the rows. This is the complementary action to regexp_split_to_table. Grouping by summing numerical fields seems to be straightforward, but grouping by concatenating text fields escapes my search of the documentation. Thanking you for your patience, John -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
