Search code examples
sqloracle-databasenullcoalesce

How to return NULL as a specific string in a SQL query result?

I am trying to return NULL as a string in the view when queried, but don't really understand how.

For the column it either has data or returns NULL, but if it returns NULL I want it to say something else. 

SELECT lib_item_id "Library Item ID",
       title "Library Item Name", 
       date_of_purchase "Year of Purchase",
       coalesce(pub_id, null) as "Publisher ID"
  FROM lib.Library_items 
 WHERE date_of_purchase > '31-DEC-10'
 ORDER BY "Library Item Name" asc;
Solution

  • Use

    select coalesce(some_column, 'some default string') as some_column
from your_table

    or

    select case when some_column is null
            then 'some default string'
            else some_column 
       end as some_column
from your_table