CODEWITHSUNDEEP
sqlfunctionnvl
Kinjal Brahmbhatt
Kinjal Brahmbhatt

Reputation: 13

nvl function not returning expected result

I know that nvl function works only for 'null', not empty string. I just wanted to know if there was a function that helps me replace blank/null values with another one.

I used instead:

CASE WHEN expression_1 is null or expression_1 = '' then expression_2 else expression_1

expression_1 column has empty cells, not null cells.

Upvotes: 1

Views: 3648

Answers (3)

The AG
The AG

Reputation: 690

I believe you are looking Coalesce ():

coalesce(expression2, expression1)

Brings the first non-null value from expression2 else expression 1. If all expressions evaluate to null, then the COALESCE function will return null.

NVL function lets you substitute a value when a null value is encountered:

nvl(expression2, expression1)

Sql statement would replace expression 2 with with expression 1 if it finds null value in there.

Difference: NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL

One awesome explanation given by @Quassnoi here

Upvotes: 0

Shantanu Kher
Shantanu Kher

Reputation: 1054

Incase the column contains blank spaces, you can use TRIM() besides NVL() so as you can gracefully remove blank spaces before you apply NVL().

NVL(TRIM(' '),'X') --> returns X

db<>fiddle - https://dbfiddle.uk/?rdbms=oracle_18&fiddle=08435972cbfb799329e6609349c70a04

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

NVL() is a function most typically associated with Oracle. The equivalent standard SQL function is COALESCE().

By default, Oracle treats NULL strings and empty strings ('') as the same. So, '' IS NULL evaluates to "true" (and '' = '' rather confusingly evaluates to NULL, which is false in a WHERE clause).

So, you can use NVL() or COALESCE() on an empty string. These are the same:

NVL(NULL, 'A')
NVL('', 'A')
COALESCE(NULL, 'A')
COALESCE('', 'A')

Here is a db<>fiddle.

Upvotes: 2

Related Questions