Guide to Oracle SQL (University at Buffalo Version)

Using Conversion Functions and Conditional Expressions

Conversion functions are used to explicitly convert data from one type to another. You can use the DESCRIBE SQL statement to identify the fields in a table and the type of data that can be stored in each field.

Implicit Data Conversion

Oracle will attempt to implicitly to try to convert data from one type to another when possible. For example, when the data type is NUMBER and you use the LENGTH() function. Oracle will convert the NUMBER into a VARCHAR2 anf the calculate the length of the string

SELECT LENGTH(1234567890)
FROM dual;
   
LENGTH(1234567890)
------------------
                10

Figure 5-1: Implicit Conversion of a NUMBER to a VARCHAR2

Another example, when the data type is DATE and you use the LENGTH() function. Oracle will convert the DATE into a VARCHAR2 anf the calculate the length of the string

SELECT LENGTH(SYSDATE)
FROM dual;
  
LENGTH(SYSDATE)
---------------
              9

Figure 5-2: Implicit Conversion of a DATE to a VARCHAR2

Sometimes an implicit conversion fails. For example, if you try to convert a string (VARCHAR2) into a number. This will produce an Oracle ORA-01722 error. See Oracle error codes for mmore information.

SELECT MOD('$11',2)
FROM dual;
           *
ERROR at line 1:
ORA-01722: invalid number

Figure 5-3: Implicit Conversion Fails Trying to Convert a VARCHAR2 to a NUMBER

Explicit Data Conversion

Oracle provides a number of conversion functions, such as TO_CHAR, TO_NUMBER, and TO_DATE, that allow you to explicitly convert between data types.

The next figure shows a SELECT statement that returns a number and then a SELECT statement that returns a number explicitly converted to string.

SELECT ename, empno 
FROM emp;

ENAME                     EMPNO
-------------------- ----------
Bikele                     7839
Wanjiru                    7566
Keino                      7788
Felix                      7876
Shorter                    7902
Bolt                       7369
Rupp                       7782
Powell                     7499

8 rows selected.
  
SELECT ename || ' (' || TO_CHAR((empno) || ')' AS Employee
FROM emp;
 
EMPLOYEE
---------------------------------------------------------------
Bikele (7839)
Wanjiru (7566)
Keino (7788)
Felix (7876)
Shorter (7902)
Bolt (7369)
Rupp (7782)
Powell (7499)

8 rows selected.

Figure 5-4: Explicit Conversion of a NUMBER to a VARCHAR2

You can also format a NUMBER or Date column in your result set using a formating mask. The next figure shows a SELECT statement that returns a number and then a SELECT statement that returns a number formatted as currency.

SELECT ename, salary 
FROM emp;

ENAME                    SALARY
-------------------- ----------
Bikele                  1000000
Wanjiru                  125000
Keino                     45000
Felix                     32989
Shorter                   42000
Bolt                      32500
Rupp                      99000
Powell                    76500

8 rows selected.
  
SELECT ename, TO_CHAR(salary, '$9,999,999') AS Salary
FROM emp;

ENAME                SALARY
-------------------- -----------
Bikele                $1,000,000
Wanjiru                 $125,000
Keino                    $45,000
Felix                    $32,989
Shorter                  $42,000
Bolt                     $32,500
Rupp                     $99,000
Powell                   $76,500

8 rows selected.

Figure 5-5: Formatting a NUMBER

SELECT last_name || ', ' || first_name, hire_date 
FROM emp;

EMPLOYEE                                        HIRE_DATE
----------------------------------------------- ---------
Oates, John                                     10-SEP-09
Hall, Darryl                                    10-OCT-08
Hall, Sarah                                     10-SEP-09
Felix, Allison                                  10-OCT-08
Jones, LoLo                                     10-SEP-12
Lindgren, Gerry                                 10-DEC-13
Scott, Steve                                    10-MAR-11
Wertz, Charlie                                  01-SEP-89
Taylor, James                                   10-MAR-09
Taylor, Livingston                              01-SEP-99

10 rows selected.  

SELECT select last_name || ', ' || first_name AS Employee, 
TO_CHAR(hire_date, 'Mon DD, YYYY') AS "Hire Date" 
FROM employees;


EMPLOYEE                                        Hire Date
----------------------------------------------- ------------
Oates, John                                     Sep 10, 2009
Hall, Darryl                                    Oct 10, 2008
Hall, Sarah                                     Sep 10, 2009
Felix, Allison                                  Oct 10, 2008
Jones, LoLo                                     Sep 10, 2012
Lindgren, Gerry                                 Dec 10, 2013
Scott, Steve                                    Mar 10, 2011
Wertz, Charlie                                  Sep 01, 1989
Taylor, James                                   Mar 10, 2009
Taylor, Livingston                              Sep 01, 1999

10 rows selected.

Figure 5-6: Formatting a DATE

You can also explicitly convert a string to a date using the TO_DATE function.

Nested, General, and Conditional Functons

Oracle has a very robust set of built-in functions. You can use these functions on a SELECT query. You can also nest these functions, including conversion functions.

Function	Description
AVG(fieldName)	Returns the average of a field in the records in the result set.
COUNT(fieldName)	Returns the number of rows in the result set.
DECODE(expr1,expr2,ifTrue,ifFalse)	Performs an if-then-else condition on expr1 compared with expr2 and returns the ifTrue or ifFalse value.
LOWER(fieldName)	Lower cases some field in a table.
MAX(fieldName)	Returns record with the largest number in a field.
MIN(fieldName)	Returns the record with the lowest number in a field.
SUBSTR(fieldName, start, num)	Returns the substring (length = num) of a field beginning with the start parameter.
SUM(fieldName)	Returns the total of some field in a table.
TRUNC(fieldName)	Returns the dd-Mon-YY and truncates the time in a `DATE field.`
UPPER(fieldName)	Upper cases some field in a table.

Figure 5-7: Some General and Aggregate SQL Functions

SELECT MAX(salary) AS Salary
FROM employees;

    SALARY
----------
    555000

SELECT MAX(TO_CHAR(salary, '$9,999,999') AS Salary
FROM employees;
	
SALARY
-----------
   $555,000

Figure 5-8: The MAX() Function

The `CASE` Expression

The CASE expression can be used to determine if a conditional expression matches one of multiple conditions. You can think of the CSAE expression as a series of if-then-elseif-then-else statements in many programming languages.

The Emplyees table has a field named job_id which contains an abbreviated term. We can use the CASE expression to provide more user-friendly text for that field.

SELECT select first_name, last_name, 
CASE 
  WHEN job_id = 'SA_MAN'
  THEN
    'SA Manager'
  WHEN job_id = 'IT_PROG'
  THEN
    'IT Programmer'
  WHEN job_id = 'MK_REP'
  THEN
    'Market Representative'
  WHEN job_id = 'ST_CLERK'
  THEN
    'Store Clerk'
  WHEN job_id = 'AD_ASST'
  THEN
    'Administrative Assistant'
  ELSE 
    'Unknown'
END job_id
FROM employees;

FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ------------------------
John                 Oates                     SA Manager
Darryl               Hall                      IT Programmer
Sarah                Hall                      SA Manager
Allison              Felix                     IT Programmer
LoLo                 Jones                     Market Representative
Gerry                Lindgren                  Store Clerk
Steve                Scott                     Administrative Assistant
Charlie              Wertz                     SA Manager
James                Taylor                    Administrative Assistant
Livingston           Taylor                    SA Manager

10 rows selected.

Figure 5-9: The CASE Expression