CODEWITHSUNDEEP
oracle-database
uday
uday

Reputation: 19

How can i use NVL function while i insert a value for null for a record

I have a scenario where i need to insert a value for NULL in a table. Can someone please help me as how i can do it. It's not only that am looking using NVL function but anything is appreciated.

Thanks, Kumar

Upvotes: 0

Views: 5634

Answers (2)

DazzaL
DazzaL

Reputation: 21993

it sounds to me like you want NVL2

ie:

NVL2(your_var, 'val if not null', 'val if null')

or case/decode can also be used

eg:

SQL>   var a varchar2(11)
SQL> exec :a := 'not null';

PL/SQL procedure successfully completed.

SQL> select nvl2(:a, 'c', 'b') "nvl2",
  2         case when :a is null then 'b' else 'c' end "case",
  3         decode(:a, null, 'b', 'c') "decode"
  4    from dual;

n c d
- - -
c c c

SQL> exec :a := '';

PL/SQL procedure successfully completed.

SQL> select nvl2(:a, 'c', 'b') "nvl2",
  2         case when :a is null then 'b' else 'c' end "case",
  3         decode(:a, null, 'b', 'c') "decode"
  4    from dual;

n c d
- - -
b b b

so if you wanted the value as-is if not null, and something hard coded if its null then:

nvl2(age, age, 42)

so if age is null it goes to 42, otherwise it takes the value of age.

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Insert into mytable (ID, Name, Age) values (1,'John Smith', Null);

Upvotes: 0

Related Questions