Built-In Single-Row Functions - Oracle DBA

The previous sections covered all the basics of a SELECT statement using DUAL and how strings and numbers are constructed, compared, and combined. Now we can start looking at some of Oracle's built-in single-row functions that operate on strings and numbers in database table columns.

Function A named set of predefined programming language commands that performs a specific task given zero, one, or more arguments and returns a value.

In both Oracle SQL and most programming languages, a function is a predefined set of steps that can be accessed using a common name. A function may include zero, one, or more arguments that are passed to the function, and it may return a result. For example, the SQRT function calculates the square root of a number and returns a value of 1.414214 when called with an argument of 2: SQRT(2) = 1.414214.

Single-Row FunctionA function that may have zero, one, or more arguments and will return one result for each row returned in a query.

Single-row functions are functions that may have zero, one, or more arguments and will return one result for each row returned in the query. Functions can be called in the SELECT, WHERE, and ORDER BY clauses of a SELECT statement.

Note All of these functions are available for use in both SQL and PL+SQL (Oracle's SQL-based programming language). As of Oracle9i, SQL and PL+SQL share the same core SQL engine.

In this section, we'll cover the highlights of Oracle's string functions, numeric functions, date functions, conversion functions, and general functions that don't fall neatly into any of the other categories.

String Functions

String functions are functions that perform some kind of transformation on a string literal, a column containing a string, or an expression consisting of string literals and table columns. String functions will return a string as the result of the transformation. Table briefly describes the built-in string functions.

Built-in string functions

Built-in string functions

Let's consider some practical uses for string functions. Now that Scott's widget company is off the ground, Scott regrets some of the shortcuts he took when creating the initial version of the database. The users don't find the reports very readable, and it would look a lot better if the names were in uppercase and lowercase letters.

Numeric FunctionA function that operates on literals, columns containing strings, or an expression containing string literals and table columns, returning a string as the result.

The INITCAP function offers a quick way to clean up names and addresses that may be in all uppercase, all lowercase, or mixed case. It will work for a first pass over the data to at least make the names and addresses somewhat readable. Until Scott can overhaul the database, he can use the INITCAP function and column aliases to make things look a bit better:

Numeric Function

Note The INITCAP function cannot capitalize mixed-case names correctly. For example, if one of the employee names were McDonald, the INITCAP function would not capitalize that name correctly (unless there was a space between MC and DONALD, which wouldn't be right either).

The next day, the Publications department wants to put the employee numbers and names on an intranet web page. The web page designers would like the employee number left justified and the employee name right justified, for a total width of 40 characters. Between the employee number and name must be a series of dots (or periods). To provide the complete 40-character field, Scott must use the LENGTH and LPAD functions in addition to what he already had from the example above:

Numeric Function

This query uses three string functions: two of them are nested within another function, plus a concatenation operation. Let's break down the query to clarify how it works.

Implicit Conversion Conversion of one datatype to another that occurs automatically when columns or constants with dissimilar datatypes appear in an expression.

As you've seen, the function call INITCAP(ename) changes the first letter of each word to uppercase. The function call LENGTH(empno) returns the length of a character string. In this case, there is an implicit conversion of a numeric type to a string type. An implicit conversion occurs automatically when Oracle evaluates an expression; conversely, an explicit conversion occurs when the SQL statement makes no assumptions about how Oracle will convert one datatype to another and uses one or more of the built-in functions to perform the conversion. The column is converted to a character string, and the length of the converted character string is returned.

Explicit Conversion Conversion of one datatype to another in an expression using function calls such as TO_CHAR instead of relying on automatic conversion rules (implicit conversion).

The LPAD function will left-pad a character string to a specified number of characters with the character you specify. Scott wants to end up with a total of 40 characters, so he subtracts the number of characters that the employee number would take up. Here, he will left-pad the employee name with periods, less the amount of space taken up by the employee number. Once the LPAD function is evaluated, he will concatenate the employee number at the front, and once again, he will allow the implicit conversion of the employee number from numeric to string.

Finally, Scott wants the title for the report to look readable, so he assigns a column alias to the result of the concatenated function calls. The column alias can act as a report title

Numeric functions

Numeric Function A function that operates on numeric literals, columns containing numbers, or an expression containing numeric literals and table columns, returning a number as the result.

Numeric functions are functions that perform some kind of transformation on a numeric literal, a column containing a number, or an expression consisting of numeric literals and table columns. Numeric functions will return a number as the result of the transformation. Table briefly describes the built-in numeric functions.

Built-in Numeric Functions

Built-in Numeric Functions

Scott's company has survived its first month and has even turned a small profit. Scott wants to find a way to distribute the first month's profit in a fair manner, so he turns to the company mathematician and statistician, Julie. She suggests that the employees get a one-time bonus that is based on the square root of their current salary. Scott can run the following query to see what the potential bonuses might be using the SQRT function:

Built-in Numeric Functions

Scott seems to like this idea, since the bonuses for the highest paid workers are not as big of a percentage of their base wage as they are for the lowest paid workers.

The report is a bit unreadable; Scott wants the bonus rounded to two digits with a better heading for the bonus. The new query looks something like this, using the ROUND function:


The report is looking better, but the Bonus column is still not formatted quite right. We'll look at ways to fix this in the section on conversion functions later in this chapter.

Since a lot of employees are on commission, Scott may want to base the bonus on both the salary and commission. We'll look at how to do this in the section on general functions.

Date Functions

Date functions are functions that perform some kind of transformation on a date literal, a column containing a date, or an expression consisting of date literals and table columns. Date functions will return a date or a string containing a portion of the date as the result of the transformation.

Built-In-Date-FunctionsBuilt-in Data Functions

Note Date and time handling has been greatly enhanced since Oracle9i. Not only can the precision of Oracle's timestamp datatypes support fractions of a second to nine decimal places, other functions and system parameters smooth the process of handling Oracle servers and sessions across multiple time zones. This is handy for companies with national and international business.

When Scott started his widget company, he hired most of the people away from a competitor. As part of the employment agreement, he kept the new employees' original hire date for the new company. He wants to see how many employees have been working for the company (or competitor) more than 250 months. He can run this query to get the answer:


Date Function A function that performs some kind of transformation on a date literal, a column containing a date, or an expression consisting of date literals and table columns. Date functions return a date or a string containing a portion of the date as the result of the transformation.

Note that there are two functions being called: SYSDATE and MONTHS_ BETWEEN. SYSDATE has no arguments; it merely returns the current date and time, so the parentheses must be omitted. The MONTHS_BETWEEN function returns the difference between dates in months. If you wanted to know the number of days instead, you would not need the MONTHS_BETWEEN function and could use the expression SYSDATE-HIREDATE instead. Date arithmetic returns values in units of days.

Conversion Functions

As the name implies, conversion functions convert between numbers, strings, and date values. The common conversion functions are described in Table.

Built-in Conversion Functions

Built-in Conversion Functions

Scott knows he can improve on the query he used to see which employees have been with the company more than 250 months. Rather than see the number of months since the original hire date, he wants to see the dates when the employee will reach or has reached the 250-month mark. For this result, he will use the NUMTOYMINTERVAL function to add 250 months to the hire date:


Scott could have used the function TO_YMINTERVAL('20-10') to add 20 years and 10 months (250 months total) to the hire date. Whether to use one method or another depends on how you want to specify the format—as a discrete number of months or years or as a combination of months and years.

Now that Scott knows more about the conversion functions, he wants to revisit one of the queries he wrote previously:

The problem with this query was that the default numeric formatting didn't look good, even after applying the ROUND function. Scott can apply another function here, TO_CHAR, to force the bonus to have two decimal places, even if the bonus does not have any significance beyond the first decimal point. The TO_CHAR function specifies the value to be formatted and the desired format, and it can be used to format both numbers and date values. Here, Scott wants to fix that rounded number:


In addition to the ‘9’ digit in the format, you can use ‘0’ to force leading zeros, a ‘$’ to show dollar amounts, a ‘-’ for leading or trailing signs, commas to make large numbers more readable, or even roman numerals. Table shows a few sample numeric formats and how the value 7322.8 would look in that format.

Numeric Format Examples Using TO_CHAR

Numeric Format Examples Using TO_CHAR

Notice that when a number will not fit into the format provided, it is displayed as all #s. Notice also that rounding will automatically occur if there are not enough positions to the right of the decimal to accommodate the entire number.

General Functions

The category of general functions covers all of the functions that don't fit neatly into a single category. Many of them are shortcuts that allow the DBA or developer to avoid needing to use PL/SQL for certain types of processing, such as a conditional operation that would normally require more than one statement. Table briefly describes the general functions.

Built-in General Functions

Built-in General Functions

Scott is continuing to analyze the profitability versus expenses in his widget company by looking at the total compensation for each employee. Most employees are salaried, but a few are salaried with a commission. Scott's first attempt at a total compensation calculation is something like this:

Built-in General Functions

Wait a minute, what happened to the salaries for the other employees? As noted earlier in the chapter, NULL values provide a great benefit in that they can indicate that a value is unknown, unavailable, or not applicable. However, when combined in some kind of calculation with non-NULL values, the result will always be NULL. For example, adding 15 to an unknown value will result in a new value that is also unknown.

In the case of the employee salaries and commissions, however, Scott wants to treat the commissions as zero if they are NULL for the purpose of calculating total compensation. For this, he will use the NVL function. NVL takes two arguments. The first argument is compared to NULL, and if it is NULL, it returns the second argument; otherwise, it returns the first argument. Scott's query can be modified with the NVL function to produce the correct results:


That looks a lot better. Other, more esoteric functions such as VSIZE are more often used by DBAs to determine how much space a particular column for a particular row is using, in bytes:


The lengths for the employee names make sense, but why would a salary of 3000 take up less space than a salary of 2450? This is because all numbers are stored internally in scientific notation. Only the 3 from the 3000 salary needs to be stored with an exponent of 3, whereas the salary 2450 is stored as 2.45 with an exponent of 3. More digits of precision require more storage space in Oracle's variable internal numeric format.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics