Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

IFNULL/NVL Functions

Description

The IFNULL function is working like the NVL function; however, only the NVL function supports collection type as well. The IFNULL function (which has two arguments) returns expr1 if the value of the first expression is not NULL or returns expr2, otherwise.

Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.

  • CHAR < VARCHAR
  • NCHAR < NCHAR VARING
  • BIT < VARBIT
  • SHORT < INT < BIGINT < NUMERIC < FLOAT < DOUBLE
  • DATE < TIMESTAMP < DATETIME

For example, if a type of a is INT and b is BIGINT, then IFNULL(a, b) returns a BIGINT type. If a type of a is INTEGER and b is TIMESTAMP, then IFNULL(a, b) returns a VARCHAR type.

Syntax

IFNULL( expr1, expr2 )

NVL( expr1, expr2

 

result:

expr1 | expr2

IFNULL(a, b) or NVL(a, b) has the same meaning as the CASE statement below.

CASE WHEN a IS NULL THEN b

ELSE a

END

Example

SELECT * FROM case_tbl;

            a

=============

            1

            2

            3

         NULL

 

--returning a specific value when a is NULL

SELECT a, NVL(a, 10.0000) FROM case_tbl;

            a  nvl(a, 10.0000)

===================================

            1  1.0000

            2  2.0000

            3  3.0000

         NULL  10.0000

 

--IFNULL can be used instead of NVL and return values are converted to the string type

SELECT a, IFNULL(a, 'UNKNOWN') FROM case_tbl;

            a   ifnull(a, 'UNKNOWN')

===================================

            1  '1'

            2  '2'

            3  '3'

         NULL  'UNKNOWN'