3 Replies Latest reply: Jul 19, 2013 5:54 AM by Sven W. RSS

    Printing NULL value in result

    user10508700

      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

       

      col1|col2|col3

      varchar2|number|varchar2

       

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

       

      varchar2||varchar2

       

      When what i want is this:

       

      varchar2|(null)|varchar2

       

      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
          Pablolee

          col||NVL(TO_CHAR(col2),'NULL')||col3

          • 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;
            
            COL1||'|'||NVL(TO_CHAR(COL2),'NULL')||'|'||COL3   
            --------------------------------------------------
            col1|1|col3                                       
            col1|null|col3
            
            • 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.

               

              Example:

               

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

               

              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.