Re: does nvl function work with characters?

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 20 Apr 1999 02:51:08 GMT
Message-ID: <371fe6db.2020039@netnews.worldnet.att.net>

On Mon, 19 Apr 1999 18:53:17 -0400, "deb" <dnanda_at_netcom.ca> wrote:

>hi,
>just wanted to know if nvl function can work with characters.....because i
>tried to substitute null values with the word 'not available' but it didnot
>work.it replaces the null value with '0' but doesnot insert characters.

It will work with characters if the column in question is a character column. If the column is numeric, then NVL must return a numeric value. When you specify '0', Oracle does an implicit conversion to make it a numeric zero.

If you need text, you might be able to do something like this:

SQL> select nvl(to_char(test_num),'not available') from test;

NVL(TO_CHAR(TEST_NUM),'NOTAVAILABLE')

---

0
not available

The above should work if you are using SQL*Plus, or some similiar tool, to generate a report.

Jonathan Received on Mon Apr 19 1999 - 21:51:08 CDT