 |
|
Alphabetic Functions
Oracle Tips by
Burleson
|
The alphabetic group of functions deals primary with operations on
strings but also provides some other interesting functions, such as
functions that deal with converting characters to and from their ASCII
equivalents.
ascii()
The ascii() function returns the ASCII value of a specified
character. For example,
ascii ('a')
returns
97
If you call ascii() using a string longer than a single
character, only the ASCII value of the first character will be
returned. For example,
ascii ('abcdefg')
returns
97
chr()
The chr() function is the counterpart of the ascii()
function. chr() returns the character associated with a
specific ASCII value. For example,
chr (97)
returns
a
initcap()
The initcap() function accepts a string as a parameter and
returns the string with the first letter of each word in the string
capitalized. For example,
initcap ('the quick brown fox...')
returns
The Quick Brown Fox...
instr()
The instr() function is one of the most useful character
functions. In its simplest incarnation, the function accepts two
parameters: a string to be searched (let’s call this string1)
and a string to be searched for (let’s call this string2)
within string1. The syntax for instr() is as follows:
instr ('JELLY BEANS AND APPLE TREES', 'E')
The instr() function returns an integer value. In our
example, the result of the function call is
2
indicating that the first occurrence of string2 in string1 occurs
at the second character.
The function can accept a third parameter (let’s call this
parameter x), which is used to indicate the starting position
for the search within string1. For example,
instr ('JELLY BEANS AND APPLE TREES', 'E', 3)
returns
8
X can be negative, which instructs Oracle to count backwards from
the end of string1 by x characters before starting the search. For
example,
instr ('JELLY BEANS AND APPLE TREES', 'E', -3)
returns
25
As you can see, the value returned by this call to instr()
is the position of string2 based on the total length of string1 and
not on the starting position for the search.
But wait, there’s more! instr() can also accept a fourth
parameter (let’s call this parameter y), which tells Oracle
which occurrence of the string2 should be returned. For example,
instr ('JELLY BEANS AND APPLE TREES', 'E', 3, 2)
returns
21
The values of the x and y parameters default to 1 (which means the
values start with the first character in string1 and find the first
occurrence of string2).
length()
The length() function accepts a string as a parameter and
returns an integer value. For example,
length ('HORSES')
returns
6
lower()
The lower() function accepts a string and converts each
character in the string to its lowercase equivalent. For example,
lower ('APPLE')
returns
apple
lpad()
The lpad() function accepts two parameters: a string (let’s
call this string1) and an integer value that represents the
desired width of the string. Using this information, the function
returns the value of string1 padded on the left side until string1 has
the desired length. For example,
lpad ('apple', 10)
returns
apple
lpad() can also accept a third parameter, string2, which
should be used to pad string1 to the desired width. For example,
lpad ('apple', 10, '*')
returns
*****apple
The third parameter of lpad() defaults to a single space (‘
’).
ltrim()
In its simplest incarnation, the ltrim() function removes
all spaces from the left side of a specified string. For example,
ltrim (' apple')
returns
apple
The ltrim() function can also accept a second parameter, a
set of characters that should be trimmed from the left side of the
string. The string is trimmed until the function reaches the first
character not appearing in the specified set. For example,
ltrim ('apple', 'ap')
returns
le
The second parameter of ltrim() defaults to a single space
(‘ ’).
replace()
In its simplest form, replace() accepts two parameters: a
string to be searched (let’s call it string1) and a string that
should be removed from string1 (let’s call it string2). For
example,
replace ('applesauce and marinara sauce', 'sauce')
returns
apple and marinara
replace() can also accept a third parameter (let’s call it
string3). When string3 is provided, all occurrences of string2
are replaced with string3. For example,
replace ('applesauce and marinara sauce', 'sauce', ' tree')
returns
apple tree and marinara tree
The third parameter of replace() defaults to NULL.
rpad()
The rpad() function is identical to the lpad()
function, with the exception that string1 is padded on the right side
instead of the left.
rtrim()
The rtrim() function is identical to the ltrim()
function, with the exception that the string is trimmed on the right
side instead of the left.
substr()
The substr() function is another extremely useful function
for parsing strings. Most calls to substr() use three
parameters. For example,
substr ('apples and oranges', 1, 6)
returns
apples
The call to substr() in this example instructs the function
to start with the first character of the specified string and return
the first six characters of the string. The third parameter can be
left out, in which case, the function starts reading at the position
indicated by the second parameter and reads until the end of the
string is reached.
The value of the second parameter can be negative, which instructs
the function to read backwards from the end of the string to find the
starting position.
translate()
The translate() function accepts three parameters. For
example,
translate ('xyz', 'x', 'w')
returns
wyz
Every occurrence of the second parameter within the string is
replaced with the third parameter. A common use of this is function is
to remove nonprinting or special characters from a string. For
example,
replace (<string variable>, chr (9), NULL)
removes all the tabs from a string of text.
upper()
The upper() function is identical to the lower()
function, with the exception that every character in the string is
converted to its uppercase equivalent.
Date Functions
There aren’t very many commonly used date functions. However, the
one function that is commonly used— SYSDATE() —is, perhaps, the
most commonly used function provided by SQL*Plus.
SYSDATE()
The SYSDATE() function returns the current date and time in
the default Oracle date format. The default format for the date
returned is
MM-DD-YY
It’s very common to use SYSDATE() in conjunction with
to_char(). For example,
to_char (SYSDATE, 'MM-DD-YYYY HH:MI:SS');
returns a string containing not only the current date, but also the
current time down to the second. The most common uses of SYSDATE()
don’t use a date format model:
dStartDate := SYSDATE;
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
