2

In this below example why other than "NAME1" all giving null as result in oracle 11g.

If I mention space explicitly it takes space otherwise null only not empty string.Please help me to clarify this.

In NAME2 I specify the empty space but still it gives null.

select 
NVL(NAME,' ') AS NAME1,
NVL(NAME,'') AS NAME2,
NVL(NAME,NULL) AS NAME3,
NAME AS NAME4 
from employee

OUTPUT :

(space),null,null,null

sunleo
  • 10,589
  • 35
  • 116
  • 196
  • 1
    You might like to get used to using Coalesce(), the ANSI version of Oracle's Nvl(). It's more flexible as it takes 2+ arguments, and features short-circuit evaluation so can be faster. – David Aldridge Mar 12 '13 at 10:05
  • Related but not quite a dupe http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null/203536#203536 – Justin Cave Sep 22 '15 at 18:43

3 Answers3

8

Because in Oracle a ZERO length varchar is treated as NULL.

In your example 

NVL(NAME, ' ') AS NAME1 will evaluate to either NAME or ' ' - empty string.
NVL(NAME, '') as NAME2 will evaluate to either NAME or a zero length string
bruno
  • 2,802
  • 1
  • 23
  • 23
3

Because '' is equal with null in oracle. For example these two queries are same:

update  test set name=''  where id = 15
update  test set name=null   where id = 15
Fariba
  • 693
  • 1
  • 12
  • 27
1

The reason is that in Oracle an empty string is equivalent to NULL.

You can see that this is true by executing the followig query

SELECT 'x' FROM DUAL WHERE '' IS NULL

This should return a single row, meaning that the condition '' IS NULL is true.

npe
  • 15,395
  • 1
  • 56
  • 55
  • *and AFAIK in SQL generally* That is **incorrect**. For instance, in SQL Server an empty string and a null string [are two different animals](http://stackoverflow.com/questions/17283978/sql-coalesce-with-empty-string) – Jan Doggen Sep 22 '15 at 08:19
  • @JanDoggen, you're right. I removed the generalisation. It's still valid for Oracle though. – npe Sep 22 '15 at 18:37