Lesson 9

Function Extension Implementations Conclusion

This module explored many of Oracle's SQL extensions that are implemented as functions.
The functions explored in this module were broken down by the datatype on which the function performs its tasks.
You observed many character functions, such as SUBSTR and LENGTH. You learned the correct syntax of these functions. Working with numbers, you saw that there were many diverse functions from which to choose. You studied examples of the more common functions, such as ROUND and TRUNC.
Oracle dates implicitly include time. There are many diverse formats to use to display a date, even though all dates are stored in the database in a special numeric format. You examined complex combinations of date, number, and character functions in the exercises and quizzes of this module.

SUBSTR, SUBSTRB, SUBSTR4

Returns a string that represents a substring of a source string. The returned substring is of a specified number of characters, beginning from a designated starting point, relative to either the beginning or end of the string.

SQL syntax

{SUBSTR | SUBSTRB | SUBSTR4}=(Source, m, n)

Parameters

SUBSTR has the parameters:

Parameter	Description
Source	The string for which this function returns a substring. Value can be any supported character data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2,CLOB, or NCLOB data types. Both TimesTen and Oracle Database data types are supported. If Source is a CHAR string, the result is a CHAR or VARCHAR2 string. If Source is a NCHAR string, the result is a NVARCHAR2 string. If Source is a LOB, the resultis the same LOB data type.
m	The position at which to begin the substring. If m is positive, the first character of the returned string is m characters from the beginning of thestring specified in char. Otherwise it is m characters from the end of the string. If ABS(m) is bigger than the length of the character string, a null value is returned.
n	The number of characters to be included in the substring. If n is omitted, all characters to the end of the string specified in char are returned. If n is less than 1 or if char, m or n is NULL, NULL is returned.

Description: SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.
Examples:In the first five rows of employees, select the first three characters of last_name:

< Kin >
< Koc >
< De >
< Hun >
< Ern >

5 rows found.
In the first five rows of employees, select the last five characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,-5,5) FROM employees;
 <NULL> 
< chhar >
< Haan >
< unold >
< Ernst >

5 rows found.

Module 4 Review

In this module, you learned how to:

List character (string) functions unique to Oracle or different in Oracle
Interpret the effect of number functions
Identify valid date-related date functions and Oracle's standard date format
Identify valid time-related date/time functions
Use DECODE and NVL to modify query results

In the next module you will review the concept of table joins. you will learn how Oracle implements outer joins and adds new functionality with special query formats.