Part 6 in this series, “A Function of Character” (Oracle Magazine, July/August 2012), introduced SQL character functions (also known as string functions or text functions) and showed how your queries can use them to modify the appearance of character result set data. Similarly, you can use SQL number functions to manipulate numerical data so that it displays differently from how it is stored in the database. This article introduces you to some of the more commonly used SQL number functions, along with some useful miscellaneous other functions.
To try out the examples in this and subsequent articles in the series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Express Edition 11g Release 2.
If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_101 is the user account you’ll use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and SYSTEM and make a note of them.
Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_101 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.) Some of the examples also use the DUAL table. Recall that DUAL is an Oracle system table owned by the SYS user, not the SQL_101 schema. DUAL contains no meaningful data itself, but it is useful to query it as a way to experiment with functions that work on literals.A Nice Round Number
One frequently used number function, ROUND, enables you to round a numeric value that is returned in a result set. For example, the simple query in Listing 1 uses this function to apply conventional rounding to two numbers. One number is rounded down, and the other is rounded up.
Code Listing 1: Using ROUND function to round one number up and another number down
SQL> set feedback on SQL> select ROUND(7534.1238, 2), ROUND(99672.8591, 2) 2 from dual; ROUND(7534.1238,2) ROUND(99672.8591,2) —————————————————— ——————————————————— 7534.12 99672.86 1 row selected.
Number functions require input parameters that are numeric—either a column with a NUMBER datatype or a numeric literal. ROUND takes two parameters, one required and one optional. The required parameter is the numeric value to be rounded. The optional parameter is an integer that indicates the rounding precision—that is, how many places to the right (indicated by a positive integer) or left (indicated by a negative integer) of the decimal point the numeric value should be rounded to. The query in Listing 1 applies the ROUND number function to two numeric literal values. Both numbers are rounded to two digits to the right of the decimal point.
If you omit the second parameter, the ROUND function will round the numeric value to the nearest whole number, as shown in Listing 2. The query in Listing 3 demonstrates that if you pass the optional parameter a negative integer, the ROUND function will round the numeric value on the left side of the decimal point.
Code Listing 2: Using ROUND function to round numeric values to whole numbers
SQL> select ROUND(7534.1238), ROUND(99672.8591) 2 from dual; ROUND(7534.1238) ROUND(99672.8591) ———————————————— ————————————————— 7534 99673 1 row selected.
Code Listing 3: Using ROUND function to round numeric values to the left of the decimal point
SQL> select ROUND(7534.1238, -1), ROUND(99672.8591, -3) 2 from dual; ROUND(7534.1238,-1) ROUND(99672.8591,-3) ——————————————————— ———————————————————— 7530 100000 1 row selected.Cutting Your Data Off
The TRUNC function returns a numeric value truncated to a certain number of decimal places. Like the ROUND function, it takes one required parameter and one optional parameter. The required parameter is the number to be truncated. The optional parameter is a positive or a negative integer. A positive integer specifies how many decimal places to truncate to. Listing 4 shows how the TRUNC function behaves when it is passed a positive value for the optional parameter. Note that the query simply truncates the returned values, leaving off any digits beyond two digits to the right of the decimal point.
Code Listing 4: Using TRUNC function to cut off digits to the right of the decimal point
SQL> select TRUNC(7534.1238, 2), TRUNC(99672.8591, 2) 2 from dual; TRUNC(7534.1238,2) TRUNC(99672.8591,2) —————————————————— ——————————————————— 7534.12 99672.85 1 row selected.
If you omit the optional parameter, the returned value will be truncated to zero decimal places, as shown in Listing 5. When you use a negative integer for the optional parameter, as shown in Listing 6, you are specifying how many digits to the left of the decimal point should be changed to 0 in the displayed results.
Code Listing 5: Using TRUNC function to truncate numeric values to whole numbers
SQL> select TRUNC(7534.1238), TRUNC(99672.8591) 2 from dual; TRUNC(7534.1238) TRUNC(99672.8591) ———————————————— ————————————————— 7534 99672 1 row selected.
Code Listing 6: Using TRUNC function to truncate numeric values to the left of the decimal point
SQL> select TRUNC(7534.1238, -1), TRUNC(99672.8591, -3) 2 from dual; TRUNC(7534.1238,-1) TRUNC(99672.8591,-3) ——————————————————— ———————————————————— 7530 99000 1 row selected.
Code Listing 5: Using TRUNC function to truncate numeric values to whole numbers
SQL> select TRUNC(7534.1238), TRUNC(99672.8591) 2 from dual; TRUNC(7534.1238) TRUNC(99672.8591) ———————————————— ————————————————— 7534 99672 1 row selected.Top to Bottom
Similar to ROUND and TRUNC are the FLOOR and CEIL number functions. The FLOOR function determines the largest integer less than (or equal to) a particular numeric value. Conversely, the CEIL function determines the smallest integer greater than (or equal to) a particular numeric value. FLOOR and CEIL (unlike ROUND and TRUNC) do not take an optional parameter for precision, because their output is always an integer. When all four of these functions are applied to a positive number, as illustrated in Listing 7, FLOOR behaves similarly to TRUNC with no optional parameter specified, and CEIL behaves similarly to ROUND with no optional parameter specified. Note, however, that FLOOR behaves like ROUND and CEIL behaves like TRUNC when these functions are applied to a negative number.
Code Listing 7: Using and comparing ROUND, CEIL, TRUNC, and FLOOR functions
SQL> select ROUND(99672.8591), CEIL(99672.8591), TRUNC(99672.8591), FLOOR(99672.8591) 2 from dual; ROUND(99672.8591) CEIL(99672.8591) TRUNC(99672.8591) FLOOR(99672.8591) ————————————————— ———————————————— ————————————————— ————————————————— 99673 99673 99672 99672 1 row selected.Arithmetic and Its Remains
The four arithmetic operators (+, –, *, and /—for addition, subtraction, multiplication, and division) can be used in SQL statements and combined with one another and any of the number functions. Listing 8 shows a query from the EMPLOYEE table that reports each employee’s annual base salary, a calculated 3 percent bonus per salary value, and the weekly salary value (including bonus) for each employee.
Code Listing 8: Arithmetic operators in combination with the ROUND number function
SQL> select first_name, last_name, salary, salary*.03 "BONUS", ROUND((salary/52)+((salary*.03)/52)) "Weekly Sal w/Bonus" 2 from employee 3 order by salary desc, last_name; FIRST_NAME LAST_NAME SALARY BONUS Weekly Sal w/Bonus —————————— ————————— ——————— —————— —————————————————— Emily Eckhardt 100000 3000 1981 michael peterson 90000 2700 1783 Donald Newton 80000 2400 1585 Frances Newton 75000 2250 1486 Matthew Michaels 70000 2100 1387 mark leblanc 65000 1950 1288 Roger Friedli 60000 1800 1188 Betsy James 60000 1800 1188 8 rows selected.
Two number functions, MOD and REMAINDER, can both be used to calculate the remainder of a value divided by another value. Both functions require two parameters: the value to be divided and the divisor. The MOD function uses the FLOOR function in its computation logic, and the REMAINDER function uses ROUND. For this reason, the values returned from the two functions can differ, as shown in Listing 9.
Code Listing 9: Differences between the MOD and REMAINDER function results
SQL> select MOD(49, 18) modulus, REMAINDER(49, 18) remaining 2 from dual; MODULUS REMAINING —————————— ————————— 13 -5 1 row selected.Replacing the Unknown with the Known
Recall that a null value in a table is the absence of a value. Null values cannot be compared with, or computed with, one another. However, you can substitute a non-null value for a NULL value by applying the NVL miscellaneous function to the NULL. The NVL function requires two input parameters: the expression (a column value, literal value, or computed result) to be tested for nullity and the expression to substitute for any NULL expressions in the results.
For example, Listing 10 shows a query that lists each employee alongside the EMPLOYEE ID value of that person’s manager. For the employees with no value for MANAGER—that is, those whose MANAGER values are NULL in the database—the results display 0 as the manager’s EMPLOYEE ID. This occurs because the query applies the NVL function to each MANAGER value, substituting 0 for any NULL.
Code Listing 10: Substitute a NULL value for MANAGER with a value of 0
SQL> select employee_id, last_name, first_name, NVL(manager, 0) manager 2 from employee 3 order by manager, last_name, first_name; EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER ——————————— ——————————— ————————————— ————————— 28 Eckhardt Emily 0 37 Newton Frances 0 6569 peterson michael 0 6567 Friedli Roger 28 6568 James Betsy 28 7895 Michaels Matthew 28 1234 Newton Donald 28 6570 leblanc mark 6569 8 rows selected.
As you can also see in Listing 10, the original value of the tested expression is returned if it is not NULL.
In the Listing 10 example, a returned value of 0 might not tell you as clearly as you’d like that certain employee records have no assigned manager value. Instead, you might prefer to return text that states this fact explicitly.
Listing 11 shows a query that tries to replace each NULL value with a more descriptive text literal. The query returns an error, however, because the NVL function requires the substitution value to be convertible to the datatype of the comparison value. However, you can obtain the textual output in a few ways. Listing 12 demonstrates one of them: the inclusion of a datatype conversion function, TO_CHAR. Datatype conversion functions will be discussed in detail in subsequent articles in this series.
Code Listing 11: Attempt to replace a returned NULL value with a text value
SQL> select employee_id, last_name, first_name, NVL(manager, 'Has no manager') manager 2 from employee 3 order by manager, last_name, first_name; select employee_id, last_name, first_name, NVL(manager, 'Has no manager') manager * ERROR at line 1: ORA-01722: invalid numberCode Listing 12: Replace a returned NULL value with a text value by using TO_CHAR
SQL> select employee_id, last_name, first_name, NVL(TO_CHAR(manager), 'Has no manager') manager 2 from employee 3 order by manager, last_name, first_name; EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER ——————————— ————————— —————————— ——————————————— 6567 Friedli Roger 28 6568 James Betsy 28 7895 Michaels Matthew 28 1234 Newton Donald 28 6570 leblanc mark 6569 28 Eckhardt Emily Has no manager 37 Newton Frances Has no manager 6569 peterson michael Has no manager 8 rows selected.Adding More Detail with DECODE
Sometimes a simple substitution function such as NVL doesn’t provide all the choices you require. The DECODE function uses if-then-else logic to give you more than one possible substitution choice.
The syntax for the DECODE function starts with an input expression. It compares that expression with a comparison value. If the two values match (this is the “if-then” portion of the DECODE logic), the DECODE function returns the substitution value. If the two values do not match, the input expression will be compared with the next available comparison value. If another comparison value is not provided, the optional default substitution value (the “else” portion of the DECODE logic) will be returned. Listing 13 demonstrates the syntax for the DECODE function. It also demonstrates how DECODE functions can be nested inside one another.
Code Listing 13: DECODE substitution function
SQL> select employee_id, first_name, last_name, DECODE(manager, 28, 'Emily Eckhardt', 6569, 'Michael Peterson', DECODE(employee_id, 28, 'Is Emily', 6569, 'Is Michael', 'Neither Emily nor Michael')) manager 2 from employee 3 order by manager, last_name, first_name; EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER ——————————— ——————————— ————————————— —————————————————— 6567 Roger Friedli Emily Eckhardt 6568 Betsy James Emily Eckhardt 7895 Matthew Michaels Emily Eckhardt 1234 Donald Newton Emily Eckhardt 28 Emily Eckhardt Is Emily 6569 michael peterson Is Michael 6570 mark leblanc Michael Peterson 37 Frances Newton Neither Emily nor Michael 8 rows selected.
The query in Listing 13, like the one in Listing 12, substitutes a textual value for the actual MANAGER value for each employee record. Note, though, that with DECODE, you can repeat the test and substitute values—that is, repeat the if-then logic—as much as you require. Another difference from NVL is that DECODE can test for conditions other than nullity; for example, the query in Listing 13 tests whether a particular value exists.
If you do want to test for nullity with DECODE, you can write a query such as
SELECT DECODE(manager, NULL, 'Has no Manager', manager) FROM employee;
In this example, if the value obtained from the MANAGER column is NULL, the “Has no manager” string will be returned. Otherwise, the non-null manager value will be returned. You might be wondering why this statement does not return an error, given that the MANAGER value is of a different datatype than the string that would be returned if the MANAGER value were NULL. The reason is implicit datatype conversion. Oracle Database implicitly converts a number to a string in situations like this example. (It does not—and cannot—convert a string to a number.) However, it is not good practice to allow Oracle Database to perform implicit datatype conversions. If you need a datatype conversion, you should always perform a call to a datatype conversion function explicitly.A Case for Comparative Searches
Although the DECODE function is more powerful than the NVL function, it cannot be (easily) used for comparisons other than equality (and inequality.) A searched CASE expression can not only be used in place of the DECODE function but can also be used more easily for greater-than or less-than comparisons.
Listing 14 shows a query that uses a searched CASE expression. As you can see, the searched CASE expression starts with the CASE keyword and ends with the END keyword. Each WHEN clause tests a condition. If a condition is true, the CASE expression will return the value specified in the associated THEN clause. Like the DECODE function’s ELSE condition, the default ELSE condition in the searched CASE expression is optional. CASE expressions can be used in WHERE clauses, as shown in Listing 15. They can even be nested, as shown in Listing 16.
Code Listing 14: Searched CASE expression in a less-than comparison
SQL> select employee_id, first_name, last_name, salary, 2 CASE WHEN manager = 28 THEN 'Emily is the manager. No bonus this year.' 3 WHEN salary < 80000 THEN 'Bonus this year.' 4 ELSE 'No bonus this year.' 5 END "Bonus?" 6 from employee 7 order by last_name, first_name; EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY Bonus? ——————————— —————————— ————————— ———————— ———————————————————————————————————————— 28 Emily Eckhardt 100000 No bonus this year. 6567 Roger Friedli 60000 Emily is the manager. No bonus this year. 6568 Betsy James 60000 Emily is the manager. No bonus this year. 7895 Matthew Michaels 70000 Emily is the manager. No bonus this year. 1234 Donald Newton 80000 Emily is the manager. No bonus this year. 37 Frances Newton 75000 Bonus this year. 6570 mark leblanc 65000 Bonus this year. 6569 michael peterson 90000 No bonus this year. 8 rows selected.
Code Listing 15: Searched CASE expression in a WHERE clause
SQL> select employee_id, first_name, last_name, salary 2 from employee 3 where salary + CASE 4 WHEN ROUND((salary/52)+((salary*.03)/52)) > 1500 5 THEN 0 6 WHEN ROUND((salary/52)+((salary*.03)/52)) < 1300 7 THEN 500 8 ELSE 200 9 END > 75000 10 order by last_name, first_name; EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY ——————————— —————————— ——————————— ——————————— 28 Emily Eckhardt 100000 1234 Donald Newton 80000 37 Frances Newton 75000 6569 michael peterson 90000 4 rows selected.
Code Listing 16: Nested searched CASE expressions
SQL> select employee_id, first_name, last_name, 2 CASE manager WHEN 28 THEN 'Emily Eckhardt' 3 WHEN 6569 THEN 'Michael Peterson' 4 ELSE 5 CASE employee_id WHEN 28 THEN 'Is Emily' 6 WHEN 6569 THEN 'Is Michael' 7 ELSE 'Neither Emily nor Michael' 8 END 9 END manager 10 from employee 11 order by manager, last_name, first_name; EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER ——————————— ——————————— ————————————— —————————————————— 6567 Roger Friedli Emily Eckhardt 6568 Betsy James Emily Eckhardt 7895 Matthew Michaels Emily Eckhardt 1234 Donald Newton Emily Eckhardt 28 Emily Eckhardt Is Emily 6569 michael peterson Is Michael 6570 mark leblanc Michael Peterson 37 Frances Newton Neither Emily nor Michael 8 rows selected.Conclusion
This article has shown you a few of the more common number functions and how you can use them to manipulate the way your data displays. You’ve seen how to round numeric data values up and down and how to truncate them. You now know how the ROUND and TRUNC number functions behave, in comparison to FLOOR and CEIL. You’ve also seen that the MOD and REMAINDER number functions can return different values because of the type of computation each one uses. Last but not least, you understand the power and differences of substitution functions such as NVL, DECODE, and searched CASE expressions.
This article has by no means provided an exhaustive list of the Oracle number and miscellaneous substitution functions. Review the documentation.
The next installment of SQL 101 will discuss date and datatype conversion functions.Next Steps
READ SQL 101, Parts 1–6
READ more about relational database design and concepts
Oracle Database Concepts 11g Release 1 (11.2)
Oracle Database SQL Language Reference 11g Release 1 (11.1)
Oracle SQL Developer User’s Guide Release 3.1
number and substitution functions
DOWNLOAD the script for this article
Photography by Meric Dagli, Unsplash