Oracle null check for string field

Question

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

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
– David Aldridge, Commented Mar 12, 2013 at 10:05
Related but not quite a dupe stackoverflow.com/questions/203493/… — Justin Cave
– Justin Cave, Commented Sep 22, 2015 at 18:43

bruno · Accepted Answer · 2013-03-12 09:44:45Z

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

answered Mar 12, 2013 at 9:44

bruno

2,8921 gold badge25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fariba · Accepted Answer · 2013-03-12 10:59:30Z

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

answered Mar 12, 2013 at 10:59

Fariba

7331 gold badge12 silver badges30 bronze badges

Comments

npe · Accepted Answer · 2015-09-22 18:37:08Z

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.

edited Sep 22, 2015 at 18:37

answered Mar 12, 2013 at 9:43

npe

15.8k1 gold badge59 silver badges59 bronze badges

2 Comments

Jan Doggen Over a year ago

and AFAIK in SQL generally That is incorrect. For instance, in SQL Server an empty string and a null string are two different animals

npe Over a year ago

@JanDoggen, you're right. I removed the generalisation. It's still valid for Oracle though.

Collectives™ on Stack Overflow

Oracle null check for string field

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related