Working with Strings（使用Oracle字符串）(五)
arily when generating reports, when you want to put spaces (or other characters) in front of or after the end of your string. For these situations, Oracle Database offers LPAD and RPAD.
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.
Code Listing 4: Examples of padding functions
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
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 TRANSLATE is the same. When replacing multiple characters, REPLACE and TRANSLATE 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.
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
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.
Gen