Guide to Oracle SQL (Buffalo State University 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
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
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.
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.
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.
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.
Returns the average of a field in the records in the result set.
Returns the number of rows in the result set.
Performs an if-then-else condition on expr1 compared with expr2 and returns the ifTrue or ifFalse value.
Lower cases some field in a table.
Returns record with the largest number in a field.
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.
Returns the total of some field in a table.
Returns the dd-Mon-YY and truncates the time in a DATE field.
Upper cases some field in a table.
Figure 5-7: Some General and Aggregate SQL Functions
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.