3 Replies Latest reply on Jul 19, 2013 10:54 AM by Sven W.

    Printing NULL value in result


      I'm running a SELECT statement on the form below:


      SELECT col1 ||'|'|| col2 ||'|'|| col3

      FROM table;


      This gives me the following outprint, which is what I need





      The problem is when col2 is returning a NULL value, then I get this:




      When what i want is this:




      Is there any way to get my query results to print NULL value as (null)?

      I've tried the NVL function, NVL(col2, 'null') but this won't work since there's a mismatch on datatypes. I can get the NULL value printed by not concatenating the columns and save the results as csv, but that does not give me the formatting I want.

        • 1. Re: Printing NULL value in result


          1 person found this helpful
          • 2. Re: Printing NULL value in result
            Purvesh K

            You can convert the Number column to Character and then check for NVL.


            See below:

            with data as
              select 'col1' col1, 1 col2, 'col3' col3 from dual union all
              select 'col1' col1, null col2, 'col3' col3 from dual
            select col1 || '|' || nvl(to_char(col2), 'null') || '|' || col3
              from data;
            • 3. Re: Printing NULL value in result
              Sven W.

              The reason why you got an error message has to do with automatic datatype conversions, that oracle will do.


              the concatanation "varchar2||number||varchar2" compares two different datatypes. Since a string is expected for the || operator to work, the number will be implicitly converted to varchar2. Meaning the database executes: varchar2||TO_CHAR(number)||varchar2. Including default nls conversion.


              The NVL function works slightly differently. It is overloaded to accept several datatypes, like varchar2, number, date and some more.

              That means there are at least 3 versions

              • NVL(varchar2,varchar2) return varchar2
              • NVL(number,number) return number
              • NVL(date,date) return date


              The parser now looks at the first parameter. In your case that was a number. That means the NVL(number,number) return number version is used.

              The second parameter that you added is a string. In this case oracle tries to implicitly convert this string into a number. But this failed (to_number('null') => error) and you got the error message.

              If this string parameter can be converted into a number, then the statement will succeed.




              select  'ABC'||NVL(cast(null as number), '123') || 'DEF'
              from dual;


              What oracle does here, are TWO implicit datatype conversions.

              first conversion: change the second parameter of nvl from string to number ('123' => 123).

              second conversion change the number output of nvl from number to string (123 => '123')


              Of cause that is a lot of unessesary stuff to do. Best is ALWAYS to use explizit datatype conversion.

              1 person found this helpful