Working with Strings（使用Oracle字符串）(三)
tion. That is not the case. Instead, “Not Equal” is displayed, because the value of l_fixed has been padded to a length of 10 with spaces. Consider the padding demonstrated in the following block; you would expect the block to display “Not Equal”:
BEGIN
IF 'Logic' = 'Logic '
THEN
DBMS_OUTPUT.put_line ('Equal');
ELSE
DBMS_OUTPUT.put_line ('Not Equal');
END IF;
END;
You should, as a result, be very careful about the use of the CHAR datatype, whether as the type of a variable, database column, or parameter.
Once you have declared a variable, you can assign it a value, change its value, and perform operations on the string contained in that variable using string functions and operators.
For the rest of this article, I focus on the VARCHAR2 datatype.
Using Built-in Functions with Strings
Once you assign a string to a variable, you most likely need to analyze the contents of that string, change its value in some way, or combine it with other strings. Oracle Database offers a wide array of built-in functions to help you with all such requirements. Let’s take a look at the most commonly used of these functions.
Concatenate multiple strings. One of the most basic and frequently needed operations on strings is to combine or concatenate them together. PL/SQL offers two ways to do this:
The CONCAT built-in function
The || (concatenation) operator
The CONCAT function accepts two strings as its arguments and returns those two strings “stuck together.” The concatenation operator also concatenates together two strings, but it is easier to use when combining more than two strings, as you can see in this example:
DECLARE
l_first VARCHAR2 (10) := 'Steven';
l_middle VARCHAR2 (5) := 'Eric';
l_last VARCHAR2 (20)
:= 'Feuerstein';
BEGIN
/* Use the CONCAT function */
DBMS_OUTPUT.put_line (
CONCAT ('Steven', 'Feuerstein'));
/* Use the || operator */
DBMS_OUTPUT.put_line (
l_first
|| ' '
|| l_middle
|| ' '
|| l_last);
END;
/
The output from this block is:
StevenFeuerstein
Steven Eric Feuerstein
In my experience, you rarely encounter the CONCAT function. Instead, the || operator is almost universally used by PL/SQL developers.
If either of the strings passed to CONCAT or || is NULL or ‘’ (a zero-length string), both the function and the operator simply return the non-NULL string. If both strings are NULL, NULL is returned.
Change the case of a string. Three built-in functions change the case of characters in a string:
UPPER changes all characters to uppercase.
LOWER changes all characters to lowercase.
INITCAP changes the first character of each word to uppercase (characters are delimited by a white space or non-alphanumeric character).
Listing 1 shows some examples that use these case-changing functions.
Code Listing 1: Examples of case-changing functions
SQL> DECLARE
2 l_company_name VARCHAR2 (25) := 'oraCLE corporatION';
3 BEGIN
4 DBMS_OUTPUT.put_line (UPPER (l_company_name));
5 DBMS_OUTPUT.put_line (LOWER (l_company_name));
6 DBMS_OUTPUT.put_line (INITCAP (l_company_name));
7 END;
8 /
ORACLE CORPORATION
oracle corporation
Oracle Corporation
Extract part of a string. One of the most commonly utilized built-in functions for strings is SUBSTR, which is used to extract a substring from a string. When calling SUBSTR, you pr