This discussion is archived
3 Replies Latest reply: Jul 19, 2013 3:54 AM by Sven W. RSS

Printing NULL value in result

user10508700 Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

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

  • 2. Re: Printing NULL value in result
    Purvesh K Guru
    Currently Being Moderated

    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. Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points