Re: [SQL] 2 questions.

From: Sferacarta Software <sferac(at)bo(dot)nettuno(dot)it>
To: pgsql-sql(at)postgresql(dot)org, Colin Dick <cdick(at)mail(dot)ocis(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [SQL] 2 questions.
Date: 1998-09-23 13:45:53
Message-ID: 14656.980923@bo.nettuno.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

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'

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message maloune 1998-09-23 14:27:41 default rights of new users
Previous Message Chris Johnson 1998-09-23 12:44:49 Re: [SQL] 2 questions.

Browse pgsql-sql by date

  From Date Subject
Next Message Sferacarta Software 1998-09-23 15:25:52 Re: [SQL] Question re: null
Previous Message Chris Johnson 1998-09-23 12:47:09 Re: [SQL] case-insensitive SORT BY?