Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter