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
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
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
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.
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.
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.
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. |
SELECT MAX(salary) AS Salary FROM employees; SALARY ---------- 555000 SELECT MAX(TO_CHAR(salary, '$9,999,999') AS Salary FROM employees; SALARY ----------- $555,000
MAX()
FunctionThe 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.
CASE
Expression