Oracle Certification - SQL Fundamentals I - Conversion Functions

Implicit Conversions

There are occasions when data is passed to functions in the incorrect format. Where possible Oracle will perform an implicit conversion from one data-type to another according to the format that a function expects.

The function LENGTH takes a character string and returns a numeric length. Here the number 123456789 has been implicitly converted into a character string:

In this example LENGTH is supplied with the current system date, the DATE is implicitly converted into a character string using the default date format (DD-MON-RR):

Explicit Conversions

Explicit conversions between data-types are very important when manipulating data in the database. The OCP exam covers their usage, and you will be expected to know how they work (i.e. understand the format masks that they use), and be able to predict the output they will generate. Typically a nested set of conversion functions will be tested, i.e.

Common Conversion Functions

The following are the more commonly used conversion functions.

TO_CHAR(n,[fmt],[nlsparam])

TO_CHAR converts number n to a VARCHAR2 using the optional format fmt. When the format mask is smaller than the number being converted a string of hashes is returned.

TO_NUMBER(string,[fmt],[nlsparam])

TO_NUMBER converts string to a NUMBER using the optional format fmt. See the format masks above.

TO_CHAR(d,[fmt],[nlsparam])

TO_CHAR converts date d to a VARCHAR2 using the optional format fmt. In this case the format model is case sensitive. The names of days and months are automatically padded unless fmt is pre-fixed with 'fm', in which case all white-space is trimmed.

The time portion of the date can be extracted with the following elements:

TO_DATE(string,[fmt],[nlsparam])

TO_DATE converts string to a DATE using the optional format fmt. See the format masks above.

NULL Conversion Functions

NVL(expr,ifnull)

The NVL function evaluates expr, if it is NULL it returns ifnull, otherwise it returns expr. The two parameters must be of the same type, or it must be possible for oracle to convert them:

NVL2(expr,ifnotnull,ifnull)

The NVL2 function is almost identical to NVL, apart from returning ifnotnull if expr is not null.

Note: Pay attention to the order of the parameters in NVL2 vs NVL it is easy to confuse the two. With NVL2 it is the 3rd parameter that is returned when the expression is null, not the second.

NULLIF(expr1,expr2)

NULLIF compares two expressions, if they are equal it returns NULL, otherwise expr1.

COALESCE(expr1,expr2...exprn)

COALESCE returns the first non-null value from it's parameter list, otherwise it returns NULL.

Conditional Functions

DECODE(expr1,comp1,iftrue1,[comp2,iftrue2...],[iffalse])

The DECODE function is like an if-then-else statement.

The CASE Expression

CASE expr
 WHEN comp1 THEN iftrue1
 [WHEN comp2 THEN iftrue2
 ...
 WHEN compn THEN iftruen
 ELSE iffalse]
END

Summary

There is going to close attention paid to all of these functions, both on their syntax and operation.

Moving on, Group Functions.