CODEWITHSUNDEEP
oracle-databaseselectnull
Svish
Svish

Reputation: 158051

Oracle: Show special text if field is null

I would like to write a select where I show the value of the field as normal except when the field is null. If it is null I'd like to show a special text, for example "Field is null". How would I best do this?

// Oracle newbie

Upvotes: 5

Views: 6486

Answers (3)

Pepto
Pepto

Reputation: 1444

Just insert the NVL PL/SQL function into your query

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

More detail here : http://www.techonthenet.com/oracle/functions/nvl.php

Upvotes: 4

Tommi
Tommi

Reputation: 8608

I like to use function COALESCE for this purpose. It returns the first non-null value from given arguments (so you can test more than one field at a time).

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

So this would also work:

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

Upvotes: 11

Frank Schmitt
Frank Schmitt

Reputation: 30775

You could also use DECODE:

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

Upvotes: 3

Related Questions