pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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'





view thread (6+ messages)  latest in thread

Message-ID: <14656.980923@bo.nettuno.it>
Permalink:  ../14656.980923@bo.nettuno.it/
Also on:    postgresql.org/message-id/14656.980923@bo.nettuno.it

 · 

reply

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