SQL Functions Revisited

I was reading “SQL Reference” manual and came across some interesting SQL functions which I thought of discussing here.

COALESCE

COALESCE function accepts a varying length list of arguments and returns the first non-NULL value/expression in the list. Oracle uses short-circuit evaluation, i.e., it starts scanning from the left for the first not null value and immediately terminates upon finding the first not-null occurrence instead of scanning all the expressions is the list.

You can use COALESCE as a variety of the CASE expression. For example,

COALESCE( expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

SQL> select coalesce(null, null, null, null,15) from dual;

COALESCE(NULL,NULL,NULL,NULL,15)
--------------------------------
                              15

SQL> select coalesce(null, 20, null, 1/0, null) from dual;

COALESCE(NULL,20,NULL,1/0,NULL)
-------------------------------
                             20

Instead of running into error (ORA-01476: divisor is equal to zero) for the above SQL, the query returns 20 as the output. This is because Oracle immediately stops searching the list when it finds the first non-null value.

EXTRACT

EXTRACT function returns the value of a specified datetime field from a datetime expression.

SQL> select extract(month from sysdate) from dual;

EXTRACT(MONTHFROMSYSDATE)
-------------------------
                        8

SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2007

NULLIF

This function takes two parameters and compares the first parameter with the second parameter. If they are equal, then the function returns null otherwise it returns the first parameter.

SQL> select nullif(1, 2) from dual;

NULLIF(1,2)
-----------
          1

SQL> select nullif(1, 1) from dual;

NULLIF(1,1)
-----------

REMAINDER

This function takes two numeric arguments (non-numeric values are implicitly converted to numeric values) and returns the remainder of expr2 divided by expr2.

SQL> select remainder(5624,54) from dual;

REMAINDER(5624,54)
------------------
                 8

Thanks for reading :-)