[Oracle]NVL2()

오라클 NVL2()

 

NVL2()

   -

 

NVL2(expr1, expr2, expr3)

의 형태

 

NVL2 let you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2, if expr1 is null, then NVL2 returns expr3.

 

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.

 

If the data types expr2 and expr3 are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. If expr2 is character or numeric data, then implicit conversion is implemented as follows :

 

• If expr2 is character data, then Oracle Database converts expr3 to the data type of expr2 before returning a value unless expr3 is null constant. In that case, a data type conversion is not necessary, and the database returns VARCAHR2 in the character set of expr2.
• If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

SQL>

SELECT
    NVL2('', 'B', 'C'),
    NVL2('A', 'B', 'C')
 FROM DUAL;

 

--> C    B