From: Sferacarta Software <sferac@bo.nettuno.it>
To: pgsql-sql@postgresql.org, Colin Dick <cdick@mail.ocis.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [SQL] 2 questions.
Date: Wed, 23 Sep 1998 15:45:53 +0200
Message-ID: <14656.980923@bo.nettuno.it> (raw)
References: <Pine.LNX.3.96.980922095650.17825A-100000@ocis.ocis.net>
Hello Colin,
martedì, 22 settembre 98, you wrote:
CD> Is there a strlen function in sql?
The SQL function is:
CHAR_LENGTH
CHARACTER_LENGTH
unfortunately it doesn't work with NULLs you have to exclude them
like this:
SELECT * FROM table
WHERE CHAR_LENGTH(field) = 0 AND field IS NOT NULL OR field IS NULL;
The above command list only the rows where "field" is equal '' or
NULL.
CD> Can you do a multiple comparison on the same field. I would like to be
CD> able to find entries that have either a null entry or a blank entry as a
CD> boolean result and sort by the boolean result.
CD> (ie: select field='' or field is null as x from table order by x;)
SELECT field
FROM table
WHERE field='' OR field IS NULL
ORDER BY field;
This should work, except for ORDER BY because NULL's will be all the
time, at bottom of list even if you specify [DESC]endent order.
I'm sorry but I don't know how to transform NULL's to '', this is also
one of my unsolved problems. We need a function like Oracle NVL() or
ANSI COALESCE() but we don't have this on PostgreSQL.
-----
PS:
For hackers only. IMHO I think we need to do something for NULLs. ;)
What do you think about?
Jose'
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: sferac@bo.nettuno.it, cdick@mail.ocis.net, pgsql-hackers@postgresql.org
Subject: Re: [SQL] 2 questions.
In-Reply-To: <14656.980923@bo.nettuno.it>
References: <Pine.LNX.3.96.980922095650.17825A-100000@ocis.ocis.net> <14656.980923@bo.nettuno.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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