CHAPTER 8

Single-Row and Conversion Functions

Exam Objectives

In this chapter, you will learn to

• 061.3.1    Describe Various Types of Functions Available in SQL

• 061.3.2    Use Character, Number, and Date Functions in SELECT Statements

• 061.4.1    Describe Various Types of Conversion Functions That Are Available in SQL

• 061.4.2    Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions

• 061.4.3    Apply Conditional Expressions in a SELECT Statement

The functions discussed in this chapter are commonly used built-in PL/SQL programs, packaged and supplied by Oracle. Some operate on numeric, date, and character data, while others convert data between the different scalar data types. Functions can be nested, and some functions are aimed at simplifying interactions with NULL. The conditional functions CASE and DECODE have the ability to display different results depending on data values, which provide if-then-else logic in the context of a SQL query.

Describe and Use Character, Number, and Date Functions in SQL

SQL functions are broadly divided into those that calculate and return a value for every row in a dataset and those that return a single aggregated value for all rows. The character case conversion functions will be examined, followed by the character manipulation, numeric, and date functions.

Defining a Function

A function is a program written to optionally accept input parameters, perform an operation, and return a single value. A function returns only one value per execution.

Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be optional and of differing data types. The second component is the data type of its resultant value. Upon execution, only one value of a predetermined data type is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

A function is often described as a black box that takes an input, performs a calculation, and returns a value. Instead of focusing on their implementation details, it is more useful to concentrate on the features that built-in functions provide.

Functions may be nested within other functions, such as F1(x, y, F2(a, b), z), where F2 takes two input parameters, a and b, and forms the third of four parameters submitted to F1. Functions can operate on any data type; the most common are character, date, and numeric data. These operands may be columns or expressions.

As an example, consider a function that calculates a person’s age. The AGE function takes one date input parameter, which is their birthday. The result returned by the AGE function is a number representing a person’s age. The black-box calculation involves obtaining the difference in years between the current date and the birthday input parameter.

Types of Functions

Functions can be broadly divided into two categories: those that operate on a single row at a time and those that process multiple rows. This distinction is vital to understanding the broader context in which functions are used.

Single-Row Functions

There are several categories of single-row functions, including character, numeric, date, conversion, and general. These are functions that operate on one row of a dataset at a time. If a query selects ten rows, the function is executed ten times, once per row with the values from that row as input to the function.

This query selects two columns of the REGIONS table along with an expression using the LENGTH function with the REGION_NAME column:

The length of the REGION_NAME column is calculated for each of the four rows in the REGIONS table; the function executes four separate times, returning one result per row.

Single-row functions manipulate the data items in a row to extract and format them for display purposes. The input values to a single-row function can be user-specified constants or literals, column data, variables, or expressions optionally supplied by other nested single-row functions. The nesting of single-row functions is a commonly used technique. Functions can return a value with a different data type from its input parameters. The preceding query shows how the LENGTH function accepts one character input parameter and returns a numeric output.

Apart from their inclusion in the SELECT list of a SQL query, single-row functions may be used in the WHERE and ORDER BY clauses.

Multiple-Row Functions

As the name suggests, this category of functions operates on more than one row at a time. Typical uses of multiple-row functions include calculating the sum or average of the numeric column values or counting the total number of records in sets. These are sometimes known as aggregation or group functions and are explored in Chapter 9.

Using Case Conversion Functions

Numerous sources, including application interfaces and batch programs, may save character data in tables. It is not safe to assume that character data has been entered in a case-consistent manner. The character case conversion functions serve two important purposes. They may be used, first, to modify the appearance of a character data item for display purposes and, second, to render them consistently for comparison operations. It is simpler to search for a string using a consistent case format instead of testing every permutation of uppercase and lowercase characters that could match the string. Remember that these functions do not alter the data stored in tables. They still form part of the read-only SQL query.

These functions expect string parameters that may consist of string literals, character column values, character expressions, or numeric and date values (which are implicitly converted into strings).

The LOWER Function

The LOWER function replaces the uppercase characters in a string with their lowercase equivalents. Its syntax is LOWER(s). The following query illustrates the usage of this function:

Assume that the current system date is 17-DEC-2014. The strings 200, sql, and 17-dec-2014 are returned. Note that date results may vary based on the session parameters. The numeric and date expressions are evaluated and implicitly converted into character data before the LOWER function is executed.

The LOWER function is used in the following condition to locate the records with the letters U and R, in any case, adjacent to each other in the LAST_NAME field:

Consider writing an alternative query to return the same results without using a case conversion function. It could be done as follows:

This query works but is cumbersome, and the number of OR clauses required increases exponentially as the length of the search string increases.

The UPPER Function

The UPPER function is the logical opposite of the LOWER function and replaces the lowercase characters in a given string with their uppercase equivalents. Its syntax is UPPER(s). The following query illustrates the usage of this function:

This query extracts the rows from the COUNTRIES table where the COUNTRY_NAME values contain the letters U, S, and A (in any case) in that order.

The INITCAP Function

The INITCAP function converts a string of characters into capitalized case. It is often used for data presentation purposes. The first letters of each word in the string are converted to their uppercase equivalents, while the remaining letters of each word are converted to their lowercase equivalents. A word is usually a string of adjacent characters separated by a space or underscore, but other characters such as the percentage symbol, exclamation mark, or dollar sign are valid word separators. Punctuation or special characters are regarded as valid word separators.

The INITCAP function can take only one parameter. Its syntax is INITCAP(s). The following query illustrates the usage of this function:

The query returns Init Cap Or Init_Cap Or Init%Cap.

Exercise 8-1: Use the Case Conversion Functions    Construct a query to retrieve a list of all FIRST_NAME and LAST_NAME values from the EMPLOYEES table where FIRST_NAME contains the character string li.

1.  Start SQL Developer or SQL*Plus and connect to the HR schema.

2.  The WHERE clause must compare the FIRST_NAME column values with a pattern of characters containing all possible case combinations of the string li. Therefore, if the FIRST_NAME contains the character strings LI, Li, lI, or li, that row must be retrieved.

3.  The LIKE operator is used for character matching, and four combinations can be extracted with four WHERE clauses separated by the OR keyword. However, the case conversion functions can simplify the condition. If the LOWER function is used on the FIRST_NAME column, the comparison can be done with one WHERE clause condition. The UPPER or INITCAP functions could also be used.

4.  Executing this statement returns employees’ names containing the characters li:

Using Character Manipulation Functions

The character manipulation functions are possibly some of the most powerful features to emerge from Oracle. Their usefulness in data manipulation is almost without peer, and many seasoned technical professionals whip together a quick script to massage data items using these functions. Nesting these functions is common. The concatenation operator (||) is generally used instead of the CONCAT function. The LENGTH, INSTR, SUBSTR, and REPLACE functions are often used together, as are RPAD, LPAD, and TRIM.

The CONCAT Function

The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The following query illustrates the usage of this function:

The second parameter to the CONCAT function is SYSDATE, which returns the current system date. This value is implicitly converted to a string to which the literal in the first parameter is concatenated. If the system date is 17-DEC-2014, the query returns the string Today is:17-DEC-2014.

Consider using the CONCAT function to join three terms to return one character string. Since CONCAT takes only two parameters, it is only possible to join two terms with one instance of this function. One solution is to nest the CONCAT function within another CONCAT function, as shown here:

The first CONCAT function has two parameters; the first is the literal Outer1, while the second is a nested CONCAT function. The second CONCAT function takes two parameters: the first is the literal Inner1, while the second is the literal Inner2. This query results in the following string: Outer1 Inner1 Inner2. Nested functions are described in a later section. The concatenation operator || discussed in Chapter 7 may also be used to join these terms.

The LENGTH Function

The LENGTH function returns the number of characters that constitute a character string. Blank spaces, tabs, and special characters are all counted by the LENGTH function. The LENGTH function takes only one string parameter. Its syntax is LENGTH(s). Consider the following query:

The LENGTH function is used to extract the COUNTRY_NAME values with lengths greater than ten characters from the COUNTRIES table.

The LPAD and RPAD Functions

The LPAD and RPAD functions, also known as left pad and right pad functions, return a string padded with a specified number of characters to the left or right of a given string, respectively. The character strings used for padding include character literals, column values, expressions, blank spaces (the default), tabs, and special characters.

The LPAD and RPAD functions take three parameters. Their syntaxes are LPAD(s, n, p) and RPAD(s, n, p), where s represents the source string, n represents the final length of the string returned, and p specifies the character string to be used as padding. If LPAD is used, the padding characters p are added to the left of the source string s until it reaches length n. If RPAD is used, the padding characters p are added to the right of the source string s until it reaches length n. Note that if the parameter n is smaller than or equal to the length of the source string s, then no padding occurs and only the first n characters of s are returned. Consider the queries shown in Figure 8-1.

Figure 8-1    Using the LPAD and RPAD functions

The first query does not pad the data, and the results are not as readable as the output from the second query. RPAD is used to add spaces where necessary to the concatenation of first_name and last_name until each name is 18 characters long, while LPAD adds spaces to the beginning of the salary value until each salary is 6 characters long.

The TRIM Function

The TRIM function removes characters from the beginning or end of character values to yield a potentially shorter item. The TRIM function takes a parameter made up of a mandatory component and an optional one. Its syntax is TRIM([trailing|leading|both] trimstring from s). The string to be trimmed (s) is mandatory. The following points list the rules governing the use of this function:

•  TRIM(s) removes spaces from both sides of the input string.

•  TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of string s if it is present.

•  TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of string s if it is present.

•  TRIM(both trimstring from s) and TRIM(trimstring from s) remove all occurrences of trimstring from the beginning and end of string s if it is present.

The preceding query returns the strings Hidden, Hidden****, and ****Hidden. Note that although one trim character is specified, multiple occurrences will be trimmed if they are consecutively present.

The INSTR Function (In-String)

The INSTR function locates the position of a search string within a given string. It returns the numeric position at which the nth occurrence of the search string begins, relative to a specified start position. If the search string is not present, the INSTR function returns zero.

The INSTR function takes two optional and two mandatory parameters. The syntax is INSTR(source string, search string, [search start position], [nth occurrence]). The default value for the search start position is 1, or the beginning of the source string. The default value for the nth occurrence is 1, or the first occurrence. Consider the following queries:

Query 1 searches from the start of the source string for the first occurrence of the hash and returns position 2. Query 2 searches for the hash from position 5 and finds the next occurrence at position 6. Query 3 searches for the hash from position 3 and finds the fourth occurrence at position 10.

The SUBSTR Function (Substring)

The SUBSTR function extracts a substring of a specified length from the source string beginning at a given position. If the start position is larger than the length of the source string, null is returned. If the number of characters to extract from a given start position is greater than the length of the source string, the segment returned is the substring from the start position to the end of the string.

The SUBSTR function takes three parameters, with the first two being mandatory. Its syntax is SUBSTR(source string, start position, [number of characters to extract]). The default number of characters to extract is equal to the number of characters from the start position to the end of the source string. Consider the following queries:

Query 1 extracts the substring beginning at position 5. Since the third parameter is not specified, the default extraction length is equal to the number of characters from the start position to the end of the source string, which is 6. Accordingly, for query 1, the substring returned is 5#7#9#. Query 2 returns the three characters occupying positions 5–7, which form the substring 5#7. Query 3 starts at position −3. The negative start position parameter instructs Oracle to commence searching three characters from the end of the string. Therefore, the start position is three characters from the end of the string, which is position 8. The third parameter is 2, which results in the substring #9 being returned.

The REPLACE Function

The REPLACE function replaces all occurrences of a search item in a source string with a replacement term. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE(source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. Consider the following queries:

The hash in query 1 is specified as the search character, and the replacement string is specified as ->. The hash symbol occurs five times in the source, and the resultant string is 1->3->5->7->9->. Query 2 does not specify a replacement string. The default behavior is therefore to replace the search string with an empty string; this, in effect, removes the search character completely from the source, resulting in the string 13579 being returned.

Using Numeric Functions

There is a range of built-in numeric functions provided by Oracle that rivals the mathematical toolboxes of popular spreadsheet packages. A significant differentiator between numeric and other functions is that they accept and return only numeric data. Oracle provides numeric functions for solving trigonometric, exponentiation, and logarithmic problems, among others. This guide focuses on three numeric single-row functions: ROUND, TRUNC, and MOD.

The Numeric ROUND Function

The ROUND function performs a rounding operation on a numeric value based on the decimal precision specified. The value returned is rounded either up or down, depending on the numeric value of the significant digit at the specified decimal precision position. If the specified decimal precision is n, the digit significant to the rounding is found (n + 1) places to the RIGHT of the decimal point. If it is negative, the digit significant to the rounding is found n places to the LEFT of the decimal point. If the numeric value of the significant digit is greater than or equal to 5, a “round up” occurs; otherwise, a “round down” occurs.

The ROUND function takes two parameters. Its syntax is ROUND(source number, decimal precision). The source number parameter represents any numeric value. The decimal precision parameter specifies the degree of rounding and is optional. If the decimal precision parameter is absent, the default degree of rounding is zero, which means the source is rounded to the nearest whole number.

Consider the decimal degrees listed in Table 8-1 for the number 1601.916. The negative decimal precision values are located to the left of the decimal point, while the positive values are found to the right.

Table 8-1    Decimal Precision Descriptions

If the decimal precision parameter is one, then the source number is rounded to the nearest tenth. If it is two, then the source is rounded to the nearest hundredth, and so on. The following queries illustrate the usage of this function:

Query 1 has a decimal precision parameter (n) of 1, which implies that the source number is rounded to the nearest tenth. Since the hundredths (n + 1) digit is 1 (less than 5), rounding down occurs, and the number returned is 1601.9. The decimal precision parameter in query 2 is 2, so the source number is rounded to the nearest hundredth. Since the thousandths unit is 6 (greater than 5), rounding up occurs, and the number returned is 1601.92. The decimal precision parameter of the query 3 is −3. Since it is negative, the digit significant for rounding is found 3 places to the left of the decimal point, at the hundreds digit, which is 6. Since the hundreds unit is 6, rounding up occurs, and the number returned is 2000. Query 4 has dispensed with the decimal precision parameter. This implies that rounding is done to the nearest whole number. Since the tenth unit is 9, the number is rounded up, and 1602 is returned.

The Numeric TRUNC Function (Truncate)

The TRUNC function performs a truncation operation on a numeric value based on the decimal precision specified. A numeric truncation is different from rounding in that it drops the numbers beyond the decimal precision specified and does not attempt to round up or down if the decimal precision is positive. However, if the decimal precision (n) is negative, the input value is zeroed down from the nth decimal position.

The TRUNC function takes two parameters. Its syntax is TRUNC(source number, decimal precision). The source number represents any numeric value. The decimal precision specifies the degree of truncation and is optional. If the decimal precision parameter is absent, the default decimal precision is zero, which means the source number is truncated to an integer value.

If the decimal precision parameter is 1, then the source number is truncated at its tenths unit. If it is 2, it is truncated at its hundredths unit, and so on. The following queries illustrate the usage of this function:

Query 1 has a decimal precision parameter of 1, which implies that the source number is truncated at its tenths unit, and the number returned is 1601.9. The decimal precision parameter (n) in query 2 is 2, so the source number is truncated at its hundredths unit, and the number returned is 1601.91. Note that this result would be different if a rounding operation were performed since the digit in position (n + 1) is 6 (greater than 5). Query 3 specifies a negative number (–3) as its decimal precision. Three places to the left of the decimal point implies that the truncation happens at the hundreds digit, as shown earlier in Table 8-1. Therefore, the source number is zeroed down from its hundreds digit (6), and the number returned is 1000. Finally, query 4 does not have a decimal precision parameter, implying that truncation is done at the whole-number degree of precision. The number returned is 1601.

The MOD Function (Modulus)

The MOD function returns the numeric remainder of a division operation. Two numbers, the dividend (number being divided) and the divisor (number to divide by), are provided, and a division operation is performed. If the divisor is a factor of the dividend, MOD returns zero since there is no remainder. If the divisor is zero, no division by zero error is returned, and the MOD function returns the dividend instead. If the divisor is larger than the dividend, then the MOD function returns the dividend as its result. This is because it divides zero times into the divisor, leaving the remainder equal to the dividend.

The MOD function takes two parameters. Its syntax is MOD(dividend, divisor). The dividend and divisor parameters represent a numeric literal, column, or expression, which may be negative or positive. The following queries illustrate the usage of this function:

Query 1 divides 6 by 2 perfectly, yielding 0 as the remainder. Query 2 divides 5 by 3, yielding 1 with remainder 2. Query 3 attempts to divide 7 by 35. Since the divisor is larger than the dividend, the number 7 is returned as the modulus value. Query 4 has a decimal fraction as the dividend. Dividing 5.2 by 3 yields 1 with remainder 2.2.

Working with Dates

The date functions provide a convenient way to solve date-related problems without needing to keep track of leap years or the number of days in particular months. We first describe storage of dates and the default date format masks before examining the SYSDATE function. We then discuss date arithmetic and the date manipulation functions: ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, ROUND, and TRUNC.

Date Storage in the Database

The database stores dates internally in a numeric format that supports the storage of century, year, month, and day details, as well as time information such as hours, minutes, and seconds. When accessing date information from a table, the default format of the results comprises two digits that represent the day, a three-letter abbreviation of the month, and two digits representing the year component. To avoid anomalies that arise with automatic type casting, the literal dates in the following queries have been converted to dates by a TO_DATE conversion function discussed later in this chapter. Exact results may vary due to NLS session parameter differences.

The SYSDATE Function

The SYSDATE function takes no parameters and returns the current system date and time according to the database server. If the database server is installed with AMERICAN as the default language, the SYSDATE function returns the DD-MON-RR components of the current server system date. If the database server is located in a different time zone from a client querying the database, the date and time returned by SYSDATE will differ from the local time on the client machine. Here is a query to retrieve the database server date:

Date Arithmetic

The following equation illustrates an important principle regarding date arithmetic:

Date1 – Date2 = Num1.

A date can be subtracted from another date. The difference between two date items represents the number of days between them. Any number, including fractions, may be added to or subtracted from a date item. In this context, the number represents a number of days. The sum or difference between a number and a date item always returns a date item. This principle implies that adding, multiplying, or dividing two date items is not permitted.

The MONTHS_BETWEEN Function

The MONTHS_BETWEEN function returns the number of months between two mandatory date parameters. Its syntax is MONTHS_BETWEEN(date1, date2). The function computes the difference in 31-day months between date1 and date2. If date1 occurs before date2, a negative number is returned. The difference between the two date parameters may consist of a whole number that represents the number of months between the two dates and a fractional component that represents the days and time remaining (based on a 31-day month) after the integer difference between years and months is calculated. A whole number with no fractional part is returned if the day components of the dates being compared are either the same or the last day of their respective months.

The following query illustrates the MONTHS_BETWEEN function:

Assume that the current date is 16-APR-2009. This query returns 1 as the number of months between 16-APR-2009 and 16-MAR-2009.

The ADD_MONTHS Function

The ADD_MONTHS function returns a date item calculated by adding a specified number of months to a given date value. The ADD_MONTHS function takes two mandatory parameters. Its syntax is ADD_MONTHS(start date, number of months). The function computes the target date after adding the specified number of months to the start date. The number of months may be negative, resulting in a target date earlier than the start date being returned. The number of months may be fractional, but the fractional component is ignored, and the integer component is used. These three queries illustrate the ADD_MONTHS function:

Query 1 returns 07-MAY-2009, since the day component remains the same if possible and the month is incremented by one. The second query has two interesting dimensions. The parameter specifying the number of months to add contains a fractional component, which is ignored. Adding two months to the date 31-DEC-2008 should return the date 31-FEB-2009, but there is no such date, so the last day of the month, 28-FEB-2009, is returned. Since the number of months added in the third query is −12, the date 07-APR-2008 is returned, which is 12 months prior to the start date.

The NEXT_DAY Function

The NEXT_DAY function returns the date when the next occurrence of a specified day of the week occurs. It takes two mandatory parameters and has the syntax NEXT_DAY(start date, day of the week). The function computes the date on which the day of the week parameter next occurs after the start date. The day of the week parameter must be a valid character value as determined by the NLS_DATE_LANGUAGE session parameter. For NLS_DATE_LANGUAGE=AMERICAN, the default values are at least the first three characters of the day name. The character values representing the days of the week may be specified in any case. The short name may be longer than three characters; for example, Sunday may be referenced as sun, sund, sunda, or Sunday. Consider the following queries:

Here, 01-JAN-2009 is a Thursday. Therefore, the next time a Tuesday occurs will be five days later, on 06-JAN-2009. The second query specifies the character literal WEDNE, which is interpreted as Wednesday. The next Wednesday after 01-JAN-2009 is 07-JAN-2009.

The LAST_DAY Function

The LAST_DAY function returns the date of the last day in the month to which the given day belongs. It takes a single mandatory parameter and has the syntax LAST_DAY(start date). The function extracts the month that the start date parameter belongs to and calculates the date of the last day of that month. The following query returns the date 31-JAN-2009:

The Date ROUND Function

The date ROUND function performs a rounding operation on a value based on a specified date precision format. The value returned is rounded either up or down to the nearest date precision format. This function takes one mandatory parameter and one optional parameter and has the syntax ROUND(source date, [date precision format]). The source date parameter represents any date item. The date precision format parameter specifies the degree of rounding and is optional. If it is absent, the default degree of rounding is day. The date precision formats include century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI).

Rounding up to century is equivalent to adding one to the current century. Rounding up to the next month occurs if the day component is greater than 16, or else rounding down to the beginning of the current month occurs. If the month falls between one and six, then rounding to year returns the date at the beginning of the current year; if not, it returns the date at the beginning of the following year. Consider the following query and its results:

Assume this query was run on 17-APR-2009 at 00:05. The first item rounds the date to the nearest day. Since the time is 00:05, which is after midnight, the date is not rounded up. The second item rounds the date to the same day of the week as the first day of the month. Since 01-APR-2009 is a Wednesday, the date returned is the Wednesday of the week in which this date occurs. Remember that, by default, the first day of the week is a Sunday. Therefore, the first Wednesday in the week beginning 12-APR-2009 is 15-APR-2009. The third item rounds the date to the beginning of the following month since the day component is 17 and returns 01-MAY-2009. The fourth item is rounded up to the date at the beginning of the current year since the month component is 4, and 01-JAN-2009 is returned.

The Date TRUNC Function

The date TRUNC function performs a truncation operation on a date value based on a specified date precision format.

The date TRUNC function takes one mandatory parameter and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component of the source date is set to midnight or 00:00:00 (00 hours, 00 minutes, and 00 seconds). Truncating at the month level sets the date of the source date to the first day of the month. Truncating at the year level returns the date at the beginning of the current year. The following query and results show four items in the SELECT list, each truncating a date literal to a different degree of precision:

Assume this query was run on 17-APR-2009 at 12:05 A.M. The first item sets the time component of 00:05 to 00:00 and returns the current day. The second item truncates the date to the same day of the week as the first day of the month (Wednesday) and returns the Wednesday in its week: 15-APR-2009. The third item truncates the date to the beginning of the current month and returns 01-APR-2009. The fourth item truncates the date to the beginning of the current year and returns 01-JAN-2009.

Exercise 8-2: Use the Character Manipulation Functions    Connect to the OE schema and construct a query that extracts the unique e-mail hostname from the CUSTOMERS.CUST_EMAIL column.

1.  Start SQL Developer and connect to the OE schema.

2.  A typical CUSTOMERS.CUST_EMAIL entry looks as follows:

[email protected]. The hostname begins immediately after the @ symbol and ends before the EXAMPLE.COM. The SUBSTR function may be used to extract this value. However, the start position and the length are still unknown. The INSTR function may be used to locate the position of the first occurrence of the @ symbol and the characters EXAMPLE.COM.

3.  A possible solution is as follows:

Describe Various Types of Conversion Functions Available in SQL

SQL conversion functions are single-row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely used conversion functions. TO_CHAR converts numeric and date information into characters, while TO_NUMBER and TO_DATE convert character data into numbers and dates, respectively.

Conversion Functions

Oracle allows columns to be defined with ANSI, DB2, and SQL/DS data types. These are converted internally to Oracle data types. Each column has an associated data type that constrains the nature of the data it can store. A NUMBER column cannot store character information. A DATE column cannot store random characters or numbers. However, the character equivalents of both number and date information can be stored in a VARCHAR2 field.

If a function that accepts a character input parameter finds a number instead, Oracle automatically converts it into its character equivalent. If a function that accepts a number or a date parameter encounters a character value, there are specific conditions under which automatic data type conversion occurs. Although implicit data type conversions are available, it is generally more reliable to explicitly convert values from one data type to another using single-row conversion functions.

Implicit Data Type Conversion

Values that do not share identical data types with function parameters are implicitly converted to the required format if possible. VARCHAR2 and CHAR data types are collectively referred to as character types. Character fields are flexible and allow the storage of almost any type of information. Therefore, DATE and NUMBER values can easily be converted to their character equivalents. These conversions are known as number to character and date to character conversions. Consider the following queries:

Both queries use the LENGTH function, which takes a character string parameter. The number 1234567890 in query 1 is implicitly converted into a character string, ‘1234567890’, before being evaluated by the LENGTH function, which returns 10. Query 2 first evaluates the SYSDATE function, which is assumed to be 07-APR-38. This date is implicitly converted into the character string ‘07-APR-38’, and the LENGTH function returns the number 9.

It is uncommon for character data to be implicitly converted into numeric data types, since the only condition under which this can occur is if the character data represents a valid number. The character string ‘11’ will be implicitly converted to a number, but ‘11.123.456’ will not be, as the following queries demonstrate:

Queries 3 and 4 implicitly convert the character strings ‘11’ and ‘11.123’ into the numbers 11 and 11.123, respectively, before the MOD function evaluates them and returns the results 1 and 1.123. Query 5 returns the error “ORA-1722: invalid number” when Oracle tries to perform an implicit character to number conversion because the string ‘11.123.456’ is not a valid number. Query 6 also fails with the invalid number error since the dollar symbol cannot be implicitly converted into a number.

Implicit character to date conversion is possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY]. D and DD represent single-digit and two-digit days of the month. MON is a three-character abbreviation, while MONTH is the full name for a month. R and RR represent single- and two-digit years. YY and YYYY represent two- and four-digit years, respectively. The separator1 and separator2 elements may be most punctuation marks, spaces, and tabs. Table 8-2 demonstrates implicit character to date conversion, listing several function calls and the results SQL Developer returns. These results assume that your system makes use of the American session defaults.

Table 8-2    Examples of Implicit Character to Date Conversion

Explicit Data Type Conversion

Functions that convert items from one data type to another are known as explicit data type conversion functions. These return a value guaranteed to be the type required and offer a safe and reliable method of converting data items.

NUMBER and DATE items can be converted explicitly into character items using the TO_CHAR function. A character string can be explicitly changed into a NUMBER using the TO_NUMBER function. The TO_DATE function is used to convert character strings into DATE items. Oracle’s format masks enable a wide range of control over character-to-number and character-to-date conversions.

Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions

This certification objective contains a systematic description of the TO_NUMBER, TO_DATE, and TO_CHAR functions, with examples. The discussion of TO_CHAR is divided into the conversion of date items to characters and numbers to characters. This separation is warranted by the availability of different format masks for controlling conversion to character values. These conversion functions exist alongside many others but are the most widely used.

Using the Conversion Functions

Many situations demand the use of conversion functions. They may range from formatting DATE fields in a report to ensuring that numeric digits extracted from character fields are correctly converted into numbers before applying them in an arithmetic expression.

Table 8-3 illustrates the syntax of the single-row explicit data type conversion functions.

Table 8-3    Syntax of Explicit Data Type Conversion Functions

Optional national language support (NLS) parameters (nls_parameters) are useful for specifying the language and format in which the names of date and numeric elements are returned. These parameters are usually absent, and the default values for elements such as day or month names and abbreviations are used. As Figure 8-2 shows, there is a publicly available view called NLS_SESSION_PARAMETERS that contains the NLS parameters for your current session. The default NLS_CURRENCY value is the dollar symbol, but this can be changed at the user session level. For example, to change the currency to the three-character string GBP, you can issue the following command:

Figure 8-2    National language support session parameters

Converting Numbers to Characters Using the TO_CHAR Function

The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. Its syntax is as follows:

The num parameter is mandatory and must be a numeric value. The optional format parameter may be used to specify numeric formatting information such as width, currency symbol, position of a decimal point, and group (or thousands) separators and must be enclosed in single quotation marks. There are other formatting options for numbers being converted into characters, some of which are listed in Table 8-4. Consider the following two queries:

Table 8-4    Numeric Format Masks

Query 1 evaluates the number 00001, removes the leading zeros, converts the number 1 into the character ‘1’, and returns the character string ‘1 is a special number’. Query 2 applies the numeric format mask ‘0999999’ to the number 00001, converting it into the character string ‘0000001’. After concatenation to the character literals, the string returned is ‘0000001 is a special number’. The zero and the six nines in the format mask indicate to the TO_CHAR function that leading zeros must be displayed and that the display width must be set to seven characters. Therefore, the string returned by the TO_CHAR function contains seven characters.

Converting Dates to Characters Using the TO_CHAR Function

You can take advantage of a variety of format models to convert DATE items into almost any character representation of a date using TO_CHAR. Its syntax is as follows:

Only the date1 parameter is mandatory; it must take the form of a value that can be implicitly converted to a date. The optional format parameter is case sensitive and must be enclosed in single quotes. The format mask specifies which date elements are extracted and whether the element should be described by a long or an abbreviated name. The names of days and months are automatically padded with spaces. These may be removed using a modifier to the format mask called the fill mode (fm) operator. By prefixing the format model with the letters fm, you instruct Oracle to trim all spaces from the names of days and months. There are many formatting options for dates being converted into characters, some of which are listed in Table 8-5. Consider the following three queries:

Table 8-5    Date Format Masks for Days, Months, and Years

If the current system date is 03/JAN/09 and the default display format is DD/MON/RR, then query 1 returns the character string ‘03/JAN/09 is today’s date’. There are two notable components in query 2. First, only the month component of the current system date is extracted for conversion to a character type. Second, since the format mask is case sensitive and ‘Month’ appears in title case, the string returned is ‘January is a special time’. There is no need to add a space in front of the literal ‘is a special time’, since the TO_CHAR function automatically pads the name of the month with a space. If the format mask in query 2 was ‘MONTH’, the string returned would be ‘JANUARY is a special time’. The fm modifier is applied to query 3, and the resultant string is ‘Januaryis a special time’. Note there is no space between January and the literal ‘is a special time’. In Table 8-5, assume the elements are operating on the date 02-JUN-1975 and the current year is 2009.

The date format elements pertaining to weeks, quarters, centuries, and other less commonly used format masks are listed in Table 8-6. The result column is obtained by evaluating the TO_CHAR function using the date 24-SEP-1000 BC, with the format mask from the format element column in the table.

Table 8-6    Less Commonly Used Date Format Masks

The time component of a date time data type is extracted using the format models in Table 8-7. The result is obtained by evaluating the TO_CHAR function using the date, including its time component 27-JUN-2010 21:35:13, with the format mask in the Format Element column in Table 8-7.

Table 8-7    Date Format Mask for Time Components

Table 8-8 lists several miscellaneous elements that may be used in date time format models. Punctuation marks are used to separate format elements. Three types of suffixes exist to format components of date time elements. Furthermore, character literals may be included in a date format model if they are enclosed in double quotation marks. The results in Table 8-8 are obtained by applying the TO_CHAR function using the date 12/SEP/08 14:31 with the format masks listed in the Description and Format Mask column.

Table 8-8    Miscellaneous Date Format Masks

The JOB_HISTORY table keeps track of jobs occupied by employees in the company. The query in Figure 8-3 retrieves a descriptive sentence about the quitting date for each employee based on their END_DATE, EMPLOYEE_ID, and JOB_ID fields. A character expression is concatenated to a TO_CHAR function call with a format model of ‘fmDay “the” ddth “of” Month YYYY’. The fm modifier is used to trim blank spaces that trail the names of the shorter days and shorter months. The two character literals enclosed in double quotation marks are the words “the” and “of”. The ‘th’ format model is applied to the ‘dd’ date element to create an ordinal day such as the 17th or 31st. The ‘Month’ format model displays the full name of the month element of the END_DATE column in title case. Finally, the YYYY format mask retrieves the four-digit year component.

Figure 8-3    TO_CHAR function with dates

Although the century component of a date is not displayed by default, it is stored in the database when the date value is inserted or updated and is available for retrieval. The DD-MON-RR format mask is the default for display and input. When inserting or updating date values, the century component is obtained from the SYSDATE function if it is not supplied. The RR date format mask differs from the YY format mask in that it may be used to specify different centuries based on the current and specified years. The century component assigned to a date with its year specified with the RR date format may be better understood by considering the following principles:

•  If the two digits of the current year and the specified year lie between 0 and 49, the current century is returned. Suppose the present date is 02-JUN-2007. The century returned for the date 24-JUL-04 in DD-MON-RR format is 20.

•  If the two digits of the current year lie between 0 and 49 and the specified year falls between 50 and 99, the previous century is returned. Suppose the current date is 02-JUN-2007. The century returned for 24-JUL-94 is 19.

•  If the two digits of the current and specified years lie between 50 and 99, the current century is returned by default. Suppose the current date is 02-JUN-1975; the century returned for 24-JUL-94 is 19.

•  If the two digits of the current year lie between 50 and 99 and the specified year falls between 0 and 49, the next century is returned. Suppose the current date is 02-JUN-1975; the century returned for 24-JUL-07 is 20.

Converting Characters to Dates Using the TO_DATE Function

The TO_DATE function returns an item of type DATE. Character strings converted to dates may contain all or just a subset of the date time elements composing a DATE. When strings with only a subset of the date time elements are converted, Oracle provides default values to construct a complete date. Components of character strings are associated with different date time elements using a format model or mask. Its syntax is as follows:

Only string1 is mandatory, and if no format mask is supplied, string1 must be implicitly convertible into a date. The optional format parameter is almost always used and is specified in single quotation marks. The format masks are identical to those listed in Tables 8-5, 8-6, and 8-7. The TO_DATE function has an fx modifier, which is similar to fm used with the TO_CHAR function. The fx modifier specifies an exact match for string1 and the format mask. When the fx modifier is specified, character items that do not exactly match the format mask yield an error. Consider the following five queries:

Query 1 evaluates the string 25-DEC-2010 and has sufficient information to convert it implicitly into a DATE item with a default mask of DD-MON-YYYY. The hyphen separator could be substituted with another punctuation character. Since no time components are provided, the time for this converted date is set to midnight, or 00:00:00. Query 2 cannot implicitly convert the string into a date because there is insufficient information and an ORA-01840: input value is not long enough for the date format; an error is returned. By supplying a format mask DD-MON to the string 25-DEC in query 3, Oracle can match the number 25 to DD and the abbreviated month name DEC to the MON component. Year and time components are absent, so the current year returned by the SYSDATE function is used and the time is set to midnight. If the current year is 2009, query 3 returns the date 25/DEC/09 00:00:00. Query 4 performs a complete conversion of a string with all the date time elements present, and no default values are supplied by Oracle. Query 5 uses the fx modifier in its format mask. Since the year component of the string is 10 and the corresponding format mask is YYYY, the fx modifier results in an ORA-01862 error being returned: “the numeric value does not match the length of the format item.”

Converting Characters to Numbers Using the TO_NUMBER Function

The TO_NUMBER function returns an item of type NUMBER. Character strings converted into numbers must be suitably formatted so that any nonnumeric components are translated or stripped away with an appropriate format mask. The syntax is as follows:

Only the string1 parameter is mandatory and if no format mask is supplied, it must be a value that can be implicitly converted into a number. The optional format parameter is specified in single quotation marks. The format masks are identical to those listed in Table 8-4. Consider the following two queries:

Query 1 cannot perform an implicit conversion to a number because of the dollar sign, comma, and period; it returns the error ORA-1722: invalid number. Query 2 matches the dollar symbol, comma, and period from the string to the format mask, and although the numeric width is larger than the string width, the number 1000.55 is returned.

Apply Conditional Expressions in a SELECT Statement

Nested functions were introduced earlier, but we offer you a formal discussion of this concept in this section. We also describe conditional functions that work with NULL values and support conditional logic in expressions.

Nested Functions

Nested functions use the output from one function as the input to another. Functions always return exactly one result. Therefore, you can reliably consider a function call in the same way as you would a literal value when providing input parameters to a function. Single-row functions can be nested to any level of depth. The general form of a function is as follows:

Substituting function calls as parameters to other functions may lead to an expression such as the following:

Nested functions are first evaluated before their return values are used as parametric input to other functions. They are evaluated from the innermost to outermost levels. The preceding expression is evaluated as follows:

1.  F3(param3.1) is evaluated, and its return value provides the third parameter to function F2 and may be called param2.3.

2.  F2(param2.1, param2.2, param2.3) is evaluated, and its return value provides the second parameter to function F1 and is param1.2.

3.  F1(param1.1, param1.2, param1.3) is evaluated, and the result is returned to the calling program.

Function F3 is nested three levels deep in this example.

Consider the following query:

There are three functions in the SELECT list, which, from inner to outer levels, are SYSDATE, LAST_DAY, and NEXT_DAY. The query is evaluated as follows:

1.  The innermost function is evaluated first. SYSDATE returns the current date. Assume that today’s date is 28-OCT-2009.

2.  The second innermost function is evaluated next. LAST_DAY(‘28-OCT-2009’) returns the date of the last day in October, which is 31-OCT-2009.

Finally, the NEXT_DAY(‘24-OCT-2009’, ‘tue’) function is evaluated, and the query returns the number of the last Tuesday of the month, which in this example is 27-OCT-2009.

Conditional Functions

Conditional logic, also known as if-then-else logic, refers to choosing a path of execution based on data values meeting certain conditions. Conditional functions return different values based on evaluating comparison conditions. Functions within this category simplify working with null values and include the NVL, NVL2, NULLIF, and COALESCE functions. Generic conditional logic is implemented by the DECODE function and the CASE expression. The DECODE function is specific to Oracle, while the CASE expression is ANSI SQL compliant.

The NVL Function

The NVL function evaluates whether a column or expression of any data type is null. If the term is null, it returns an alternative not-null value; otherwise, the original term is returned.

The NVL function takes two mandatory parameters; its syntax is NVL(original, ifnull), where original represents the term being tested and ifnull is the result returned if the original term evaluates to null. The data types of the original and ifnull parameters must always be compatible. Either they must be of the same type or it must be possible to implicitly convert ifnull to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter. Consider the following three queries:

Since the NVL function takes two mandatory parameters, query 1 returns the error ORA-00909: invalid number of arguments. Query 2 returns 1234 after the null keyword is tested and found to be null. Query 3 involves a nested SUBSTR function that attempts to extract the fourth character from a three-character string that returns null, leaving the outer function NVL(null,’No substring exists’) to execute, which then returns the string ‘No substring exists’.

The NVL2 Function

The NVL2 function provides an enhancement to NVL but serves a similar purpose. It evaluates whether a column or expression of any data type is null or not. If the first term is not null, the second parameter is returned, or else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null.

The NVL2 function takes three mandatory parameters with the syntax NVL2(original, ifnotnull, ifnull), where original represents the term being tested. ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. Either they must be of the same type or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter. Consider the following queries:

The ifnotnull term in query 1 is a number, and the ifnull parameter is a string. Since there is a data type incompatibility between them, an “ORA-01722: invalid number” error is returned. Query 2 returns the ifnull parameter, which is 5678. Query 3 extracts the characters bc using the SUBSTR function and the NVL2(‘bc’,’Not bc’,’No Substring’) function is evaluated and the ifnotnull parameter, ‘Not bc’, is returned.

The NULLIF Function

The NULLIF function tests two terms for equality. If they are equal, the function returns a null, or else it returns the first of the two terms tested.

The NULLIF function takes two mandatory parameters of any data type. Its syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned. Consider the following queries:

Query 1 returns a null since the parameters are identical. The character literals in query 2 are not implicitly converted to DATE items and are compared as two character strings by the NULLIF function. Since the strings are of different lengths, the ifunequal parameter 24-JUL-2009 is returned.

Figure 8-4 shows how NULLIF is nested as a parameter to the NVL2 function. The NULLIF function itself has the SUBSTR and UPPER character functions embedded in the expression used as its ifunequal parameter. The EMAIL column is compared with an expression, formed by concatenating the first character of the FIRST_NAME to the uppercase equivalent of the LAST_NAME column, for employees with four-character first names. When these terms are equal, NULLIF returns a null, or else it returns the evaluated ifunequal parameter. This is used as a parameter to NVL2. The NVL2 function provides descriptive text classifying rows as matching the pattern or not.

Figure 8-4    The NVL2 and NULLIF functions

The COALESCE Function

The COALESCE function returns the first not-null value from its parameter list. If all its parameters are null, then null is returned.

The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2, . . . , exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate:

The data type returned by COALESCE, if a not-null value is found, is the same as that of the first not-null parameter. To avoid an “ORA-00932: inconsistent data types” error, all not-null parameters must have data types compatible with the first not-null parameter. Consider the following three queries:

Query 1 returns the fourth parameter, which is a string since this is the first not-null parameter encountered. Query 2 returns null because all its parameters are null. Query 3 evaluates its first parameter, which is a nested SUBSTR function, and finds it to be null. The second parameter is not null, so the string ‘Not bc’ is returned.

The DECODE Function

The DECODE function implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.

The DECODE function takes at least three mandatory parameters, but it can take many more. The syntax of the function is DECODE(expr1, comp1, iftrue1, [comp2, iftrue2 . . . [ compN, iftrueN]], [iffalse]). These parameters are evaluated as shown in the following pseudocode example:

Here, expr1 is compared with comp1. If they are equal, then iftrue1 is returned. If expr1 is not equal to comp1, then what happens next depends on whether the optional parameters comp2 and iftrue2 are present. If they are, then expr1 is compared to comp2. If they are equal, then iftrue2 is returned. If not, what happens next depends on whether further compn, iftrueN pairs exist, and the cycle continues until no comparison terms remain. If no matches have been found and if the iffalse parameter is defined, then iffalse is returned. If the iffalse parameter does not exist and no matches are found, a null is returned.

All parameters to the DECODE function may be expressions. The return data type is the same as that of the first matching comparison item. The expression expr1 is implicitly converted to the data type of the first comparison parameter comp1. As the other comparison parameters comp2 . . . compn are evaluated, they too are implicitly converted to the same data type as comp1. DECODE considers two nulls to be equivalent, so if expr1 is null and comp3 is the first null comparison parameter encountered, then the corresponding result parameter iftrue3 is returned. Consider the following queries:

Query 1 compares the number 1234 with the first comparison term 123. Since they are not equal, the first result term cannot be returned. Further, as there is no default iffalse parameter defined, a null is returned. Query 2 is identical to the first except that an iffalse parameter is defined. Therefore, since 1234 is not equal to 123, the string ‘No match’ is returned. Query 3 searches through the comparison parameters for a match. The strings comp1 and comp2 are not equal to search, so the results true1 and true2 are not returned. A match is found in the third comparison term comp3 (parameter 6), which contains the string search. Therefore, the third result term iftrue3 (parameter 7) containing the string ‘true3’ is returned. Note that since a match has been found, no further searching takes place. So, although the fourth comparison term (parameter 8) is also a match to expr1, this expression is never evaluated because a match was found in an earlier comparison term.

The CASE Expression

Virtually all third- and fourth-generation programming languages implement a case construct. Like the DECODE function, the CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression.

The CASE expression takes at least three mandatory parameters but can take many more. Its syntax depends on whether a simple or a searched CASE expression is used. The syntax for the simple CASE expression is as follows:

The simple CASE expression is enclosed within a CASE . . . END block and consists of at least one WHEN . . . THEN statement. In its simplest form, with one WHEN . . . THEN statement, the search_expr is compared with the comparison_expr1. If they are equal, then the result iftrue1 is returned. If not, a null value is returned unless an ELSE component is defined, in which case the default iffalse value is returned. When more than one WHEN . . . THEN statement exists in the CASE expression, searching for a matching comparison expression continues until a match is found.

The search, comparison, and result parameters can be column values, expressions, or literals but must all be of the same data type. Consider the following query:

The search expression derived from the SUBSTR(1234, 1, 3) is the character string 123. The first WHEN . . . THEN statement compares the string 134 with 123. Since they are not equal, the result expression is not evaluated. The second WHEN . . . THEN statement compares the string 1235 with 123, and again, they are not equal. The third WHEN . . . THEN statement compares the results derived from the CONCAT(‘1’,’23’) expression, which is 123, to the search expression. Since they are identical, the third results expression, ‘123 is a match’, is returned.

The LAST_NAME and HIRE_DATE columns for employees with DEPARTMENT_ID values of 100 are retrieved along with two numeric expressions and one CASE expression, as shown in Figure 8-5.

Figure 8-5    The simple CASE expression

Assume that SYSDATE is 27-JAN-2015. The numeric expression aliased as YEARS returns a truncated value obtained by dividing the months of service by 12. Five categories of loyalty classification based on years of service are defined. This forms the search expression in the CASE statement. None of the rows in the dataset matches the comparison expression in the first WHEN . . . THEN statement, but as Figure 8-5 shows, four rows met the remaining WHEN . . . THEN statements and two rows are caught by the ELSE statement.

The syntax for the searched CASE expression is as follows:

The searched CASE expression is enclosed within a CASE . . . END block and consists of at least one WHEN . . . THEN statement. In its simplest form with one WHEN . . . THEN statement, condition1 is evaluated; if it is true, then the result iftrue1 is returned. If not, a null value is returned unless an ELSE component is defined, in which case the default iffalse value is returned. When more than one WHEN . . . THEN statement exists in the CASE expression, searching for a matching comparison expression continues until one is found. The query to retrieve the identical set of results to those obtained in Figure 8-5, using a searched CASE expression, is as follows:

Exercise 8-3: Use the DECODE Function    Query the HR.LOCATIONS table for rows with the value US in the COUNTRY_ID column. An expression aliased as LOCATION_INFO is required to evaluate the STATE_PROVINCE column values and returns different information as per the following table. Sort the output based on the LOCATION_INFO expression.

1.  Start SQL Developer or SQL*Plus and connect to the HR schema.

2.  The LOCATION_INFO expression may be calculated in several different ways. This includes using a CASE expression or a DECODE function. This solution uses a CASE expression:

Two-Minute Drill

Describe Various Types of Conversion Functions Available in SQL

•  Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value. The TO_CHAR function performs date-to-character and number-to-character data type conversions.

•  Character items are explicitly transformed into date values using the TO_DATE conversion function.

•  Character items are changed into number values using the TO_NUMBER conversion function.

Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions

•  The TO_CHAR function returns an item of type VARCHAR2.

•  Format models or masks prescribe patterns that character strings must match to facilitate accurate and consistent conversion into number or date items.

•  Character terms, like month and day names, extracted from dates with the TO_CHAR function are automatically padded with spaces that may be trimmed by prefixing the format mask with the fm modifier.

•  The TO_DATE function has an fx modifier that specifies an exact match for the character string to be converted and the date format mask.

Apply Conditional Expressions in a SELECT Statement

•  Nested functions use the output from one function as the input to another.

•  The NVL function returns either the original item unchanged or an alternative item if the initial term is null.

•  The NVL2 function returns a new if-null item if the original item is null or an alternative if-not-null item if the original term is not null.

•  The NULLIF function tests two terms for equality. If they are equal, the function returns null, or else it returns the first of the two terms tested.

•  The COALESCE function returns the first not-null value from its parameter list. If all its parameters are null, then a null is returned.

•  The DECODE function and the simple CASE and searched CASE expressions are used to facilitate if-then-else conditional logic.

Self Test

1.  Which statements regarding single-row functions are true? (Choose all that apply.)

A.  They may return more than one value.

B.  They execute once for each row processed.

C.  They may have zero or more input parameters.

D.  They must have at least one mandatory parameter.

2.  What value is returned after executing the following statement?

(Choose the best answer.)

A.  long

B.  _long

C.  ring?

D.  None of the above

3.  What value is returned after executing the following statement?

(Choose the best answer.)

A.  4

B.  14

C.  12

D.  None of the above

4.  What value is returned after executing the following statement?

(Choose the best answer.)

A.  3

B.  42

C.  2

D.  None of the above

5.  What value is returned after executing the following statement? Take note that 01-JAN-2009 occurred on a Thursday.

(Choose the best answer.)

A.  07-JAN-2009

B.  31-JAN-2009

C.  Wednesday

D.  None of the above

6.  Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement?

(Choose the best answer.)

A.  31-DEC-2007

B.  01-JAN-2008

C.  01-JAN-2007

D.  None of the above

7.  Choose any incorrect statements regarding conversion functions. (Choose all that apply.)

A.  TO_CHAR may convert date items to character items.

B.  TO_DATE may convert character items to date items.

C.  TO_CHAR may convert numbers to character items.

D.  TO_DATE may convert date items to character items.

8.  If SYSDATE returns 12-JUL-2009, what is returned by the following statement?

(Choose the best answer.)

A.  12TH JULY

B.  12th July

C.  TWELFTH JULY

D.  None of the above

9.  What value is returned after executing the following statement?

(Choose the best answer.)

A.  SPANIEL

B.  TERRIER

C.  NULL

D.  None of the above

10.  If SYSDATE returns 12-JUL-2009, what is returned by the following statement?

(Choose the best answer.)

A.  TAX DUE

B.  PARTY

C.  02

D.  None of the above

Self Test Answers

1.      B and C. Single-row functions execute once for every record selected in a dataset and may take either no input parameters, like SYSDATE, or many input parameters.
    A and D are incorrect. A is incorrect because a function, by definition, returns only one result, and D is incorrect because there are many functions with no parameters.

2.      A. The SUBSTR function extracts a four-character substring from the given input string starting with and including the fifth character. The characters at positions 1 to 4 are How_. Starting with the character at position 5, the next four characters form the word long.
    B, C, and D are incorrect. B is a five-character substring beginning at position 4, while ring?, which is also five characters long, starts five characters from the end of the given string.

3.      B. The INSTR function returns the position that the nth occurrence of the search string may be found after starting the search from a given start position. The search string is the underscore character, and the third occurrence of this character starting from position 5 in the source string occurs at position 14.
    A, C, and D are incorrect. They are incorrect since position 4 is the first occurrence of the search string and position 12 is the third occurrence if the search began at position 1.

4.      C. When 14 is divided by 3, the answer is 4 with remainder 2.
    A, B, and D are incorrect because the MOD function returns the remainder of a division.

5.      A. Since the first of January 2009 falls on a Thursday, the date of the following Wednesday is six days later.
    B, C, and D are incorrect. B returns the last day of the month in which the given date falls, and C returns a character string instead of a date.

6.      C. The date TRUNC function does not perform rounding, and since the degree of truncation is YEAR, the day and month components of the given date are ignored and the first day of the year it belongs to is returned.
    A, B, and D are incorrect. A returns the last day in the month in which the given date occurs, and B returns a result achieved by rounding instead of truncation.

7.      D. Dates are converted into character strings using TO_CHAR only, not the TO_DATE function.
    A, B, and C are incorrect. A, B, and C are correct statements.

8.      A. The DD component returns the day of the month in uppercase. Since it is a number, it does not matter, unless the th mask is applied, in which case that component is specified in uppercase. MONTH returns the month spelled out in uppercase.
    B, C, and D are incorrect. B would be returned if the format mask was ‘fmddth Month’, and C would be returned if the format mask was ‘fmDDspth MONTH’.

9.      A. The NULLIF function compares its two parameters, and since they are different, the first parameter is returned. The NVL2(‘CODA’, ‘SPANIEL’, ‘TERRIER’) function call returns SPANIEL, since its first parameter is not null.
    B, C, and D are incorrect. B would be correct if the NULLIF function returned a NULL, which would only happen if its two parameters were identical, and they are not. C would be correct if the second parameter to NVL2 was NULL, but it is not.

10.      B. The innermost function TO_CHAR(SYSDATE, ‘MM’) results in the character string 07 being returned. The outer function is DECODE(‘07’,’02’,’TAX DUE’,’PARTY’). Since 07 is not equal to 02, the else component PARTY is returned.
    A, C, and D are incorrect. A would be returned only if the month component extracted from SYSDATE was 02. C and D are not options for output in the DECODE function parameter list.