Advanced Oracle SQL: Null Functions Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Null functions can translate NULL into a value and inversely . Any type can contain NULL:  number, dates, strings.

WITH
   X
AS
(
   SELECT
      COLUMN_VALUE X
   FROM
      TABLE(SYS.ODCIVARCHAR2LIST('X',NULL))
),
   Y
AS
(
   SELECT
      COLUMN_VALUE Y
   FROM
      TABLE(SYS.ODCIVARCHAR2LIST('Y',NULL))
),
   Z
AS
(
   SELECT
      COLUMN_VALUE Z
   FROM
      TABLE(SYS.ODCIVARCHAR2LIST('Z',NULL))
)
SELECT
   X,
   Y,
   Z,
   NVL(X,Y),
   NVL2(X,Y,Z),
   COALESCE(X,Y,Z)
FROM
   X,Y,Z;

X      Y      Z      NVL(X,Y) NVL2(X,Y,Z) COALESCE(X,Y,Z)
------ ------ ------ -------- ----------- ---------------
X      Y      Z      X        Y           X
X      Y      <NULL> X        Y           X
X      <NULL> Z      X        <NULL>      X
X      <NULL> <NULL> X        <NULL>      X
<NULL> Y      Z      Y        Z           Y
<NULL> Y      <NULL> Y        <NULL>      Y
<NULL> <NULL> Z      <NULL>   Z           Z
<NULL> <NULL> <NULL> <NULL>   <NULL>      <NULL>

NVL returns Y when X is null and X when X is not null. NVL2 returns Y when X is not null and Z when X is null. COALESCE returns the first non-null expression.

SELECT
   ENAME,
   COMM,
   NULLIF(COMM,0)
FROM
   EMP
WHERE
   DEPTNO=30;

ENAME            COMM NULLIF(COMM,0)
---------- ---------- --------------
ALLEN             300            300
WARD              500            500
MARTIN           1400           1400
BLAKE      <NULL>     <NULL>
TURNER              0 <NULL>
JAMES      <NULL>     <NULL>
 

NULLIF returns NULL if both arguments are equal; otherwise, the first argument.

SELECT
   ENAME,
   SAL,
   COMM
FROM
   EMP
WHERE
   SAL<=1550
   AND
   LNNVL(COMM!=0);

ENAME             SAL       COMM
---------- ---------- ----------
SMITH             800 <NULL>
TURNER           1500          0
ADAMS            1100 <NULL>
JAMES             950 <NULL>
MILLER           1300 <NULL>                

LNNVL is a function that returns TRUE when the condition passed as parameter is either FALSE or NULL.

Conclusion

Oracle SQL functions have been improved in 11g including new functions such as adding a regular expression function called REGEXP_COUNT.  This chapter covers many SQL functions that were implemented in previous Oracle versions and gives detailed explanations of their attributes as well as introducing new functions.

Exercises

1.      Binary

What is the result of the following expression?

1024 + 1025 - 2 * BITAND(1024, 1025)

2.      Sign

Prior to Oracle 8i, developers were often coding expressions like:

DECODE(SIGN(X - Y), 1, ...)

What is the DECODE expression checking?

3.      Formatting

Which string is returned by the following expressions?

TO_CHAR(SYSDATE, 'CCYY')

And:

TO_CHAR(
   1E5,
   'L999G990D00',
   'NLS_CURRENCY=''NPR ''NLS_NUMERIC_CHARACTERS=''.'''''''
)

4.      Searching

What is the position returned by the following expression?

INSTR('MISSISSIPI', 'I', -1, 2)

5.      Regular expression

Which expression matches integers from 0 to 1000?

'^(10{3}|\d{1,3}?)$'

Or:

'(^1000$|^[[:digit:]][[:digit:]]?[[:digit:]]?$)'

Or:

'^(10{3}|[0-9]{0,2}[0123456789])$'

6.      Nulls

When is the following condition true?

WHERE NVL2(X,NULLIF(X,Y),Y) IS NULL

Solutions

1.      Binary

1024 + 1025 - 2 * BITAND(1024, 1025)

1, A+B-(A AND B)-(A AND B) is equivalent to (A XOR B)

2.       Sign

DECODE(SIGN(X - Y), 1, ...)

Decode is checking if X>Y. Prior to 8i, there is no CASE SQL expression.

3.      Formatting

TO_CHAR(SYSDATE, 'CCYY')

2108, CC is the century, current century is 21^st :

TO_CHAR(
   1E5,
   'L999G990D00',
   'NLS_CURRENCY=''NPR ''NLS_NUMERIC_CHARACTERS=''.'''''''
)

NPR 100'000.00

L is the local currency, defined as 'NPR ', the dot is '.' and the group separator is a single quote.

4.      Searching

INSTR('MISSISSIPI', 'I', -1, 2)

8, the second 'I' starting from the right is in the eighth position, counting from the left.

5.      Regular expression

Which expression matches integers from 0 to 1000?

'^(10{3}|\d{1,3}?)$'

Or:

'(^1000$|^[[:digit:]][[:digit:]]?[[:digit:]]?$)'

Or:

'^(10{3}|[0-9]{0,2}[0123456789])$'

All three expressions matches integer from 0 to 1000.

6.      Nulls

When is the following condition true?

WHERE NVL2(X,NULLIF(X,Y),Y) IS NULL

The condition is equivalent to:

WHERE X=Y OR (X IS NULL AND Y IS NULL)