Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use nvl() and nvl2() functions in Oracle". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

nvl() and nvl2() functions in Oracle:

The function nvl(expression1, expression2) returns the value of parameter 1 or parameter 2 depending on whether parameter 1 is null or not;

Function nvl2(expression1,expression2,expression3) returns the value of parameter 2 or parameter 3 depending on whether parameter 1 is null.

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

nvl(expression1,expression2)

Function Description:

If expression1 is null, the function returns expression2;

If expression1 is not null, the function returns expression1;

If expression1 and expression2 are null, the 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 the same.

[Example 1]: Expression1 is null:

Expression2 is character type:

select nvl(null,'ABCD') from dual; --returns: ABCDselect nvl(null,'abcd') from dual; --returns: abcdselect nvl(null,'12345') from dual; --Return: 12345

Expression2 is numeric:

select nvl(null,12345) from dual; --returns: 12345select nvl(null,00000) from dual; --returns: 0select nvl(null,-12345) from dual; --returns: -12345select nvl(null,123.45) from dual; --returns: 123.45select nvl(null,-123.45) from dual; --Return: -123.45

expression2 is date-time type:

select nvl(null,sysdate) from dual; --Back: 2022/2/25 11:54:18select nvl(null,to_date('2022/2/25 11:54:18','y-mm-dd hh34:mi:ss')) from dual; --Back: 2022/2/25 11:54:18select nvl(null,to_date('2022/2/25','y-mm-dd')) from dual; --return: 2022/2/25select nvl(null,to_date('2022','yy')) from dual; --Return: 2022/2/1 [Sample Display II]: expression1 Value is not null:

Expression1 is character type:

select nvl('ABCD','abcd') from dual; --returns: ABCDselect nvl ('abcd ',' ABCD') from dual; --returns: abcdselect nvl ('12345 ',' 54321') from dual; --Return: 12345

Expression1 is numeric:

select nvl(12345,null) from dual; --returns: 12345select nvl(0000,11111) from dual; --returns: 0select nvl(-12345, null) from dual; --returns: -12345select nvl(123.45,-123.45) from dual; --returns: 123.45select nvl(-123.45, 123. 45) from dual; --Return: -123.45

expression1 is date-time type:

select nvl(sysdate,null) from dual; --Back: 2022/2/25 12:18:23select nvl(to_date('2022/2/25 12:18:23','y-mm-dd hh34:mi:ss'),null) from dual; --Back: 2022/2/25 12:18:23select nvl(to_date('2022/2/25','y-mm-dd'),null) from dual; --return: 2022/2/25select nvl(to_date('2022','yy'),null) from dual; --Return: 2022/2/1 [Sample Display III]: expression1, expression2 values are null: select nvl(null,null) from dual; --returns null select nvl ('',null) from dual; --returns null select nvl(null,'') from dual; --returns null select nvl (',') from dual; --Returns null 2.nvl2: Returns the value of parameter 2 or parameter 3 depending on whether parameter 1 is null

nvl2(expression1,expression2,expression3)

Function Description:

If the expression1 value is not null, the function returns the expression2 value;

If expression1 is null, the function returns expression3.

If expression1, expression2, and expression3 are 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. If they are inconsistent, expression3 will automatically be converted to the type of expression2 when expression1 is null. 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 types:

select nvl2(0,'ABCD','abcd') from dual; --returns: ABCDselect nvl2 ('a ',' ABCD','1234') from dual; --returns: ABCDselect nvl2(sysdate,'1234',' abcd') from dual; --Return: 1234

Expression2 and expression3 are numerical:

select nvl2(0,12345,54321) from dual; --returns: 12345select nvl2 ('a ', 123.45,543.21) from dual; --returns: 123.45select nvl2(sysdate,-12345,-54321) from dual; --Return: -12345

Expression2 and expression3 have different data types:

Note: In this case, expression1 has a value, so the function returns the value of expression2 directly, even if the type of expression3 cannot be converted to the type of expression2.

select nvl2(0,'ABCD',54321) from dual; --returns: ABCDselect nvl2 ('a ',' abcd', 543.21) from dual; --returns: abcdselect nvl2(sysdate,'12345', -54321) from dual; --Return: 12345 [Sample Display II]: expression1 value is null:

Expression2 and expression3 are both character types:

select nvl2(null,'ABCD','abcd') from dual; --returns: abcdselect nvl2 ('','ABCD',' 1234') from dual; --Return: 1234

Expression2 and expression3 are numerical:

select nvl2(null,12345,54321) from dual; --Returns: 54321select nvl2 (',123.45,543.21) from dual; --Return: 543.21

Expression2 and expression3 have different data types:

Note: In this case, the expression1 value is null, so the function returns 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; --returns: 54321select nvl2 ('','abcd', 543.21) from dual; --returns: 543.21 select nvl2 (',543.21,'abcd') from dual; select nvl2(null,'abcd',sysdate) from dual; --returns: 25-Feb-22select nvl2(null,sysdate,'abcd') from dual; --Execution error [Sample display 3]: expression1 is null, and expression2 or expression3 value is null: select nvl2(null,null,'123') from dual; --returns: 123select nvl2(null,'abc', null) from dual; --returns: null select nvl2(null,null,null) from dual; --return: null select nvl2 (',',') from dual; --Return: null value "Oracle nvl() and nvl2() function how to use" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.