Database Reference
In-Depth Information
The next sections define all of the functions highlighted in Figures 9.1
and 9.2, divided by their categories. Functions detailed in this chapter are
generally the more useful functions. As already stated, the remaining func-
tions tend to be obscure and seldom used. In fact, some functions included
in this chapter are obscure. Let's begin with string functions.
9.2.1
String Functions
The string functions manipulate alphanumeric data. In this section, after
each function is defined, an example shows how the function is used and
what it returns.
CONCAT(expression, expression)
. Concatenation of strings is the
adding together of two strings. This function performs the same task
as the string concatenation operator || (see Chapter 7).
CONCAT('Oracle',' Database 10g') = 'Oracle Database 10g'
'Oracle'||' Database '||'10g' = 'Oracle Database 10g'
'My name is '||FIRST_NAME = 'My name is Jim'
LOWER(expression)
,
UPPER(expression
), and
INITCAP(expres-
sion)
. LOWER converts to lowercase, UPPER to uppercase, and
INITCAP to mixed case (first letter of each word in uppercase).
INITCAP('oracle certified professional')
= 'Oracle Certified Professional'
INSTR(expression, substring [, position [, occurrence]])
. Returns
the position of a substring within a string (the first character in the
string is at position 1). The position and occurrence parameters are
optional. The position parameter determines a start point to search
from, and occurrence indicates which duplicate, if any, of the sub-
string should be matched. In the following example, the second
occurrence of the string 10g begins at position 19:
INSTR('oracle 10g oracle 10g oracle 10g','10g',1,2) = 19
LENGTH(expression)
. The length in characters of a string.
LENGTH('oracle certified professional') = 29
LENGTH(LAST_NAME) = length of the data in the column
LPAD(expression, n [, expression])
and
RPAD(expression, n [,
expression])
. Left or right pad a string from the left or the right (start
or end of the string) with the specified characters in the second string,
up to a string length of
n
characters.
