nvl function not returning expected result

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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