General Functions in SQL

These functions work with any data type and pertain to using nulls.

• NVL (expr1,expr2): Converts a null value to an actual value
• NVL2 (expr1,expr2,expr3): If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
• NULLIF (expr1,expr2): Compares two expressions and returns null if they are equal, or the first expression if they are not equal.
• COALESCE ( expr1,expr2,....., expr n): Returns the first non-null expression in the expression list.

Using NVL Function

• Data types that can be used are date, character, and number.
• Data types must match:
• NVL ( comm,0 )
• NVL (hiredate,'01-JAN-13')
• NVL(job,'No Job Yet')

Example:

Using the NVL2 function

• The NVL2 function examines the first expression. 
• If the first expression is not null, then the NVL2 function returns the second expression. 
• If the first expression is null, then the third expression is returned.

Syntax:

NVL2(expr1,expr2,expr3)

In the syntax:

expr1 : is the source value or expression that may contain null.

expr2: is the value returned if exp1 is null.

expr3: is the value returned if expr2 is null.

Example:

Using the NULLIF Function

• The NULLIF function compares two expressions. 
• If they are equal, the function returns null. 
• If they are not equal the function returns the first expression. 
• You cannot specify the literal NULL for first expression.

Syntax: 

 NULLIF(expr1,expr2)

In the syntax:

expr1  is the source value compared to expr2.

expr2  is the source value compared with expr1. (if it is not equal to expr1, expr1 is returned.)

Example:

In the above example, no employee having salary=their annual commision. 

So everytime in the result tab, first value is returned. 

If in the case any employee having sal= his annual commission (12*comm) then we should have noticed null in RESULT tab.

Using the COALESCE Function

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
• If the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
• This function finds the first non null expression and returns. Thats it. 

Syntax:

              COALESCE(expr1,expr2,......exprn)

In the Syntax:

expr1  returns this expression if it is not null.

expr2  returns this expression if the first expression is null and this expression is not null.

exprn  returns this expression if the preceding expression are null.

Example:

Conditional Expression  covers in Next post.

• CASE expression
• DECODE function