Nesting Functions

SELECT ename, UPPER(SUBSTR (ENAME, 1, 8) || '_US') FROM emp WHERE deptno = 10;
The slide example displays the last names of employees in department 10.

The evaluation of the SQL statement involves three steps:

• – The inner function retrieves the first eight characters of the last name.

• Result1 = SUBSTR (ENAME, 1, 8)
• – The outer function concatenates the result with _US.

• Result2 = Result1 || '_US'
• – The outermost function converts the results to uppercase.
  

Using the TO_CHAR Function with Numbers

TO_CHAR(number, 'format_model')

• These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character :
• SELECT empno, TO_CHAR(sal, '$99,999.00') as Month_Hired FROM emp;

Using the TO_NUMBER and TO_DATE Functions :

Convert a character string to a number format using the TO_NUMBER function:

• TO_NUMBER(char[, 'format_model'])

Convert a character string to a date format using the TO_DATE function:

• TO_DATE(char[, 'format_model'])