Re: [SQL] why these results?
Yes. Thanks to all that responded. That was it. -wes On Mon, Aug 1, 2011 at 5:01 PM, Steve Crawford wrote: > On 08/01/2011 03:50 PM, Wes James wrote: >> >> select count(*) from table; >> >> count >> --- >> 100 >> (1 row) >> >> >> is correct >> >> select count(*) from table where col::text ~~* '%text%'; >> >> count >> --- >> 1 >> (1 row) >> >> is correct. >> >> But now if I do: >> >> >> select count(*) from table where col::text !~~* '%text%'; >> count >> --- >> 98 >> (1 row) >> >> Shouldn't it be 99? That is out of 100 records there is one that has >> "text" in column "col" so the !~~* should return 99 rows. ?? >> >> -wes >> > select count(*) from table where col is null; > > (null is neither equal nor not-equal to anything, even null) > > Cheers, > Steve > > -- > 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] Help with regexp-query
I am struggling a bit to do the following type of update in a table.
I want the content of a field updated like this:
Original:
'0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
After update:
'|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
in other words: change all multiple adjacent occurences of '|' to only 1.
I have tried the following query but it fails:
select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
akb_articles limit 100
This ends with 'ERROR: invalid regular expression: quantifier operand
invalid'.
I would apreciate some help with this one please.
Regards
Johann
--
Johann SpiesTelefoon: 021-808 4699
Databestuurder / Data manager
Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.
"If any of you lack wisdom, let him ask of God, that
giveth to all men liberally, and upbraideth not; and
it shall be given him." James 1:5
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] to_char() accepting invalid dates?
ERROR at line 1:
ORA-01839: date not valid for month specified
That error is coming on
select to_date('20110231', 'MMDD') from dual;
because there are not 31 days in February 2011, try this:
select to_date('20110228', 'MMDD') from dual;
Jared Thompson
Internet Operations Group
Office: 678.406.2895
[email protected]
This e-mail and any files transmitted with it are AT&T property, are
confidential, and are intended solely for the use of the individual or
entity to whom this email is addressed. If you are not one of the named
recipient(s) or otherwise have reason to believe that you have received
this message in error, please notify the sender and delete this message
immediately from your computer. Any other use, retention, dissemination,
forwarding, printing, or copying of this e-mail is strictly prohibited.
[SQL] Re: [SQL] Help with regexp-query
select id, regexp_replace(category, (E'\\|{2,}'), E'\|', 'g') as category from
akb_articles limit 100
Backslash in regex doubled. Added global modifier to replace all occurrences.
- Reply message -
From: "Johann Spies"
Date: Thu, Jul 28, 2011 8:20 am
Subject: [SQL] Help with regexp-query
To:
I am struggling a bit to do the following type of update in a table.
I want the content of a field updated like this:
Original:
'0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
After update:
'|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
in other words: change all multiple adjacent occurences of '|' to only 1.
I have tried the following query but it fails:
select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
akb_articles limit 100
This ends with 'ERROR: invalid regular expression: quantifier operand
invalid'.
I would apreciate some help with this one please.
Regards
Johann
--
Johann SpiesTelefoon: 021-808 4699
Databestuurder / Data manager
Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.
"If any of you lack wisdom, let him ask of God, that
giveth to all men liberally, and upbraideth not; and
it shall be given him." James 1:5
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Aggregating results across multiple partitions
Hi I see that some queries are not Order(n) where n=number of partitions. However, if one were to run the query separately against different partitions and aggregate the results it could be Order(n). Can such an approach be implemented in a more generic manner in pgsql? Thanks Mike
Re: [SQL] Help with regexp-query
Johann Spies wrote:
> I am struggling a bit to do the following type of update in a table.
> I want the content of a field updated like this:
> Original:
> '0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
> After update:
> '|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
> in other words: change all multiple adjacent occurences of '|' to only 1.
> I have tried the following query but it fails:
> select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
> akb_articles limit 100
> This ends with 'ERROR: invalid regular expression: quantifier operand
> invalid'.
> I would apreciate some help with this one please.
You need to double the backslashes (e. g. "E'\\|{2,}'");
otherwise the parser will "eat" the first backslash and pass
just "|{2,}" as the second argument to regexp_replace().
Tim
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
