Detailed explanation of nvl() and nvl2() function examples in Oracle

This article brings you relevant knowledge about Oracle. The function of the NVL function is to convert null values ​​and return the response based on whether the value of the first expression is null. Column name or expression, the following article mainly introduces you to the relevant information about the nvl() and nvl2() functions in Oracle. I hope it will be helpful to you.

Recommended tutorial: "Oracle Video Tutorial"

Detailed explanation of nvl() and nvl2() functions in Oracle:

• Function nvl(expression1,expression2) returns the value of parameter 1 or parameter 2 based on whether parameter 1 is null;
• Function nvl2(expression1,expression2,expression3) returns based on whether parameter 1 is null The value of parameter 2 or parameter 3.

1.nvl: Return the value of parameter 1 or parameter 2 depending on whether parameter 1 is null

[Function format]: nvl(expression1,expression2)

[Function description]:

• If the value of expression1 is null, the function returns expression2;
• If the value of expression1 is not null, the function returns expression1;
• If the values ​​of expression1 and expression2 are both null, this function returns null.

[Special Note]: Parameters expression1 and expression2 can be character type, numeric type or date type, but the types of parameters expression1 and expression2 must be consistent.

[Sample display one]: expression1 value is null:

expression2 is character type:

select nvl(null,'ABCD') from dual;        --返回：ABCD
select nvl(null,'abcd') from dual;        --返回：abcd
select nvl(null,'12345') from dual;        --返回：12345

expression2 is numeric type:

select nvl(null,12345) from dual;        --返回：12345
select nvl(null,00000) from dual;        --返回：0
select nvl(null,-12345) from dual;        --返回：-12345
select nvl(null,123.45) from dual;        --返回：123.45
select nvl(null,-123.45) from dual;        --返回：-123.45

expression2 is Date and time type:

select nvl(null,sysdate) from dual;        --返回：2022/2/25 11:54:18
select nvl(null,to_date('2022/2/25 11:54:18','yyyy-mm-dd hh24:mi:ss')) from dual;                        --返回：2022/2/25 11:54:18
select nvl(null,to_date('2022/2/25','yyyy-mm-dd')) from dual;        --返回：2022/2/25
select nvl(null,to_date('2022','yyyy')) from dual;        --返回：2022/2/1

[Sample display 2]: expression1 value is not null:

expression1 is character type:

select nvl('ABCD','abcd') from dual;        --返回：ABCD
select nvl('abcd','ABCD') from dual;           --返回：abcd
select nvl('12345','54321') from dual;        --返回：12345

expression1 is numeric type:

select nvl(12345,null) from dual;        --返回：12345
select nvl(00000,11111) from dual;        --返回：0
select nvl(-12345,null) from dual;        --返回：-12345
select nvl(123.45,-123.45) from dual;        --返回：123.45
select nvl(-123.45,123.45) from dual;        --返回：-123.45

expression1 is date and time type:

select nvl(sysdate,null) from dual;        --返回：2022/2/25 12:18:23
select nvl(to_date('2022/2/25 12:18:23','yyyy-mm-dd hh24:mi:ss'),null) from dual;                        --返回：2022/2/25 12:18:23
select nvl(to_date('2022/2/25','yyyy-mm-dd'),null) from dual;        --返回：2022/2/25
select nvl(to_date('2022','yyyy'),null) from dual;        --返回：2022/2/1

[Sample display three]: The values ​​​​of expression1 and expression2 are both null:

select nvl(null,null) from dual;        --返回空值
select nvl('',null) from dual;        --返回空值
select nvl(null,'') from dual;        --返回空值
select nvl('','') from dual;        --返回空值

2.nvl2: Return based on whether parameter 1 is null. The value of parameter 2 or parameter 3

[Function format]:nvl2(expression1,expression2,expression3)

[Function description]:

• If the value of expression1 is not is null, the function returns the expression2 value;
• If the expression1 value is null, the function returns the expression3 value;
• If the expression1, expression2, and expression3 values ​​are all null, the function returns null.

[Special Note]:

• The type of expression1 does not need to be consistent with the types of expression2 and expression3;
• The types of expression2 and expression3 should be consistent as much as possible Consistent. If not, when expression1 is null, expression3 will be automatically converted to the type of expression2. If the two data types cannot be converted, an error will be reported.

[Sample Display 1]: expression1 value is not null:

expression2 and expression3 are both character type:

select nvl2(0,'ABCD','abcd') from dual;        --返回：ABCD
select nvl2('a','ABCD','1234') from dual;        --返回：ABCD
select nvl2(sysdate,'1234','abcd') from dual;        --返回：1234

expression2 and expression3 are both numeric type :

select nvl2(0,12345,54321) from dual;        --返回：12345
select nvl2('a',123.45,543.21) from dual;        --返回：123.45
select nvl2(sysdate,-12345,-54321) from dual;        --返回：-12345

The data types of expression2 and expression3 are inconsistent:

Note: At this time, expression1 has a value, so the function directly returns the value of expression2, even if the type of expression3 cannot be converted to the type of expression2. An error will be reported.

select nvl2(0,'ABCD',54321) from dual;        --返回：ABCD
select nvl2('a','abcd',543.21) from dual;        --返回：abcd
select nvl2(sysdate,'12345',-54321) from dual;        --返回：12345

[Sample display 2]: expression1 value is null:

expression2 and expression3 are both character type:

select nvl2(null,'ABCD','abcd') from dual;        --返回：abcd
select nvl2('','ABCD','1234') from dual;        --返回：1234

expression2 and expression3 are both numeric type:

select nvl2(null,12345,54321) from dual;        --返回：54321
select nvl2('',123.45,543.21) from dual;        --返回：543.21

The data types of expression2 and expression3 are inconsistent:

Note: At this time, the value of expression1 is null, so the function will return the value of expression3. If the type of expression3 cannot be converted to the type of expression2, an error will be reported. .

select nvl2(null,'ABCD',54321) from dual;        --返回：54321
select nvl2('','abcd',543.21) from dual;        --返回：543.21
select nvl2('',543.21,'abcd') from dual;        --执行报错
select nvl2(null,'abcd',sysdate) from dual;        --返回：25-2月 -22
select nvl2(null,sysdate,'abcd') from dual;        --执行报错

[Sample display three]: expression1 is null, and expression2 or expression3 value is null:

select nvl2(null,null,'123') from dual;        --返回：123
select nvl2(null,'abc',null) from dual;        --返回：空值
select nvl2(null,null,null) from dual;        --返回：空值
select nvl2('','','') from dual;        --返回：空值

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Detailed explanation of nvl() and nvl2() function examples in Oracle. For more information, please follow other related articles on the PHP Chinese website!