Working with Strings（使用Oracle字符串）(六)
erally, 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.
Code Listing 6: Examples of LTRIM and RTRIM functions
DECLARE
a VARCHAR2 (40)
:= 'This sentence has too many periods....';
b VARCHAR2 (40) := 'The number 1';
BEGIN
DBMS_OUTPUT.put_line (
RTRIM (a, '.'));
DBMS_OUTPUT.put_line (
LTRIM (
b
, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
|| 'abcdefghijklmnopqrstuvwxyz'));
END;
The output from this block is:
This sentence has too many periods
1
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.
The default behavior of both RTRIM and LTRIM is to trim spaces from the beginning or end of the string. Specifying RTRIM(a) is the same as asking for RTRIM(a,’ ‘). The same goes for LTRIM(a) and LTRIM(a,’ ‘).
The other trimming function is just plain TRIM. TRIM works a bit differently from LTRIM and RTRIM, as you can see in this block:
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));
--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!
.....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:
TRIM(BOTH ',.;' FROM x)
If you need to remove more than one character from the front and back of a string, you 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:
BEGIN
/* Remove