Re: [SQL] 2 questions.

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'