Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.8.8/8.8.8) with ESMTP id JAA05879; Wed, 23 Sep 1998 09:46:00 -0400 (EDT) (envelope-from sferac@bo.nettuno.it) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id PAA16633; Wed, 23 Sep 1998 15:45:43 +0200 (MDT) Received: from blu.sferacarta.com (mail.sferacarta.com) [10.20.30.12] by proxy.sferacarta.com with smtp (Exim 1.92 #1 (Debian)) id 0zLr0T-0002XQ-00; Wed, 23 Sep 1998 15:39:05 +0000 Date: Wed, 23 Sep 1998 15:45:53 +0200 From: Sferacarta Software X-Mailer: The Bat! (v1.017) UNREG Reply-To: Sferacarta Software Organization: Sferacarta Software Priority: Normal Message-ID: <14656.980923@bo.nettuno.it> To: pgsql-sql@postgresql.org, Colin Dick CC: pgsql-hackers@postgresql.org Subject: Re: [SQL] 2 questions. References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit 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'