Oracle Magazine - September/October 2011

When you call these functions, you specify the length to which you want your string padded and with what character or characters. If you do not specify any pad characters, Oracle Database defaults to padding with spaces.

Listing 4 shows some examples that use these LPAD and RPAD padding functions. Replace characters in a string. Oracle Database provides a number of functions that allow you to selectively change one or more characters in a string. You might need, for example, to replace all spaces in a string with the HTML equivalent (“ ”) so the text is displayed properly in a browser. Two functions take care of such needs for you:

•;REPLACE replaces a set or pattern of characters with another set.

•;TRANSLATE translates or replaces individual characters.

Listing 5 shows some examples of these two character-replacement built-in functions. Notice that when you are replacing a single character, the effect of REPLACE and INSTR is the same. When replacing

Code Listing 3: Examples of INSTR function

multiple characters, REPLACE and INSTR act differently. The call to REPLACE asked that appearances of “abc” be replaced with “123.” If, however, any of the individual characters (a, b, or c) appeared in the string outside of this pattern (“abc”), they would not be replaced.

The call to TRANSLATE, however, specified that any occurrence of each of the individual characters be replaced with the character in the third argument in the same position.

Generally, you should use REPLACE whenever you need to replace a pattern of characters, while TRANSLATE is best applied to situations in which you need to replace or substitute individual characters in the string. Remove characters from a string. What LPAD and RPAD giveth, TRIM, LTRIM, and RTRIM taketh away. Use these trim functions to remove characters from either the beginning (left) or end (right) of the string. Listing 6 shows an example of both RTRIM and LTRIM.

RTRIM removed all the periods, because
the second argument specifies the character
(or characters) to trim, in this case, a period.
The call to LTRIM demonstrates that you can
specify multiple characters to trim. In this
case, I asked that all letters and spaces be
trimmed from the beginning of string b, and
I got what I asked for.

DECLARE x VARCHAR2 ( 30)

:= '.....Hi there!.....';

BEGIN
DBMS_OUTPUT.put_line (
TRIM (LEADING '.' FROM x));
DBMS_OUTPUT.put_line (
TRIM (TRAILING '.' FROM x));
DBMS_OUTPUT.put_line (
TRIM (BOTH '.' FROM x));

BEGIN
/* Find the location of the first "e" */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein', 'e'));

/* Find the location of the first "e" starting from position 6 */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'

, 'e'

, 6));

/* Find the location of the first "e" starting from the 6th position from
the end of string and counting to the left. */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'

, 'e'

, - 6));

/* Find the location of the 3rd "e" starting from the 6th position from
the end of string. */
DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'
, 'e'

, - 6 , 3));

END;

/

The output from this block is:

--The default is to trim
--from both sides
DBMS_OUTPUT.put_line (
TRIM ('.' FROM x));

--The default trim character --is the space: DBMS_OUTPUT.put_line (TRIM (x));

END;

The output from this block is:

Hi there!.....

.....Hi there!

Hi there!

.....Hi there!.....

With TRIM, you can trim from either side or from both sides. However, you can specify only a single character to remove. You cannot, for example, write the following: