Oracle Magazine - September/October 2011

DECLARE l_first VARCHAR2 ( 10) := 'Steven'; l_last VARCHAR2 ( 20) := 'Feuerstein'; l_phone VARCHAR2 ( 20) := '773-426-9093';

BEGIN
/* Indent the subheader by 3 characters */
DBMS_OUTPUT.put_line ('Header');
DBMS_OUTPUT.put_line (
LPAD ('Sub-header', 13, '.'));

/* Add "123" to the end of the string, until the 20 character is reached.*/
DBMS_OUTPUT.put_line (
RPAD ('abc', 20, '123'));

/* Display headers and then values to fit within the columns. */
DBMS_OUTPUT.put_line (
/*1234567890x12345678901234567890x*/
'First Name Last Name Phone');

DBMS_OUTPUT.put_line (
RPAD (l_first, 10)
|| ' '

|| RPAD (l_last, 20) || ' '

|| l_phone);

END;

/

The output from this block is:

Header ...Sub-header abc12312312312312312 First Name Last Name Phone Steven Feuerstein 773-426-9093

Code Listing 5: Examples of character replacement functions

DECLARE l_name VARCHAR2 ( 50) := 'Steven Feuerstein';

BEGIN
/* Replace all e's with the number 2. Since you are replacing a single
character, you can use either REPLACE or TRANSLATE. */
DBMS_OUTPUT.put_line (
REPLACE (l_name, 'e', ' 2'));
DBMS_OUTPUT.put_line (
TRANSLATE (l_name, 'e', ' 2'));

/* Replace all instances of "abc" with "123" */
DBMS_OUTPUT.put_line (
REPLACE ('abc-a-b-c-abc'

, 'abc'

, '123'));

/* Replace "a" with " 1", "b" with " 2", "c" with " 3". */
DBMS_OUTPUT.put_line (
TRANSLATE ('abc-a-b-c-abc'
, 'abc'

, '123'));

END;

/

The output from this block is:

St2v2n F2u2rst2in St2v2n F2u2rst2in 123-a-b-c-123 123-1-2-3-123

need to use RTRIM and LTRIM:

RTRIM(LTRIM(x,',.;'),',.;')

You can also use TRANSLATE to remove characters from a string by replacing them with (or “translating” them into) NULL. You must, however, take care with how you specify this replacement. Suppose I want to remove all digits (0 through 9) from a string. My first attempt yields the following block:

BEGIN
/* Remove all digits (0-9)
from the string. */
DBMS_OUTPUT.put_line (
TRANSLATE ('S1t2e3v4e56n'
, '1234567890'
, ''));

END;

/

When I execute this block, however, nothing (well, a NULL string) is displayed. This happens because if any of the arguments passed to TRANSLATE are NULL (or a zero-length string), the function returns a NULL value.

So all three arguments must be non-NULL, which means that you need to put at the start of the second and third arguments a character that will simply be replaced with itself, as in the following: