Geek Answers Handbook

Oracle: show special text if field is null

I would like to write select, where I show the field value as normal, unless the field is NULL. If it is null, I would like to show special text, for example "Field is null". How am I best to do this?

// Oracle newbie
+5
source share
3 answers

I like to use the function COALESCEfor this purpose. It returns the first nonzero value from the given arguments (so that you can test more than one field at a time).

SELECT COALESCE(NULL, 'Special text') FROM DUAL

So this will also work:

SELECT COALESCE(
   First_Nullable_Field, 
   Second_Nullable_Field, 
   Third_Nullable_Field, 
   'All fields are NULL'
) FROM YourTable
+11
source

Just insert PL / SQL NVL function into your query

SELECT NVL (SOMENULLABLEFIELD, 'Field Is Null') SOMENULLABLEFIELD FROM MYTABLE;

: http://www.techonthenet.com/oracle/functions/nvl.php

+4

DECODE:

select value, decode(value, NULL, 'SPECIAL', value) from 
  (select NULL value from dual
   union all
   select 2 value from dual
  )
+3
source

More articles:


All Articles