Table 4.8. Expressions using the OR operator.

String Operators

PL/SQL has two operators specifically designed to operate only on character string data. These are the LIKE operator and the concatenation (||) operator. The LIKE operator is a comparison operator used for pattern matching and was described earlier in the section titled "Comparison Operators," so only the concatenation operator is described here.

The Syntax for the Concatenation Operator

string_1 || string_2

In this syntax, string_1 and string_2 are both character strings and can be string constants, string variables, or string expressions. The concatenation operator returns a result consisting of all the characters in string_1 followed by all the characters in string_2.

Listing 4.6 shows several ways in which you can use the concatenation operator.

INPUT
Listing 4.6. Use of the concatenation operator.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   a     VARCHAR2(30);
 4:   b     VARCHAR2(30);
 5:   c     VARCHAR2(30);
 6: BEGIN
 7:   --Concatenate several string constants.
 8:   c := `Jack' || ` AND ` || `Jill';
 9:   DBMS_OUTPUT.PUT_LINE(c);
10:   --Concatenate both string variables and constants.
11:   a := `went up';
12:   b := `the hill';
13:   DBMS_OUTPUT.PUT_LINE(a || ` ` || b || `,');
14:   --Concatenate two string variables.
15:   a := `to fetch a `;
16:   b := `pail of water.';
17:   c := a || b;
18:   DBMS_OUTPUT.PUT_LINE(c);
19: END;
20: /

OUTPUT

Jack and Jill
went up the hill,
to fetch a pail of water.
PL/SQL procedure successfully completed.

ANALYSIS
The preceding code shows the concatenation operator used in several different ways. Notice that you do not always have to assign the result directly to a string variable. For example, in line 13, the concatenation operator is used to create a string expression that is passed as input to the PUT_LINE procedure.

Use of Comparison Operators with
Strings

You can use any of the PL/SQL comparison operators to compare one character string to another. Strings can be compared for equality, for inequality, to see if one string is less than another, to see if one string matches a given pattern, and so on. When using character strings in comparison expressions, the result depends on several things:

• Character set
• Datatype
• Case (upper versus lower)

The Effect of Character Set on String Comparisons

When comparing two strings to see if one is less than another or greater than another, the result depends on the sort order of the underlying character set being used. In the typical ASCII environment, all lowercase letters are actually greater than all uppercase letters, digits are less than all letters, and the other characters fall in various places depending on their corresponding ASCII codes. However, if you were working in an EBCDIC environment, you would find that all the digits were greater than the letters and all lowercase letters are less than all uppercase letters, so be careful.

The Datatype's Effect on String Comparisons

NEW TERM
The underlying datatype has an effect when comparing two string variables or when comparing a string variable with a constant. Remember that variables of the CHAR datatype are fixed length and padded with spaces. Variables of the VARCHAR2 datatype are variable length and are not automatically padded with spaces. When comparing two CHAR datatypes, Oracle uses blank-padded comparison semantics. This means that Oracle conceptually adds enough trailing spaces to the shorter string to make it equal in length to the longer string and then does the comparison. Trailing spaces alone will not result in any differences being found between two springs. Oracle also does the same thing when comparing two string constants. However, when one of the values in a comparison is a variable-length string,

Oracle uses non-padded comparison semantics. The use of non-padded comparison semantics means that Oracle does not pad either of the values with spaces, and any trailing spaces will affect the result. Listing 4.7 shows several string comparisons that illustrate this point.

INPUT
Listing 4.7. Demonstration of string comparison semantics.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: DECLARE
 3:   fixed_length_10  CHAR(10);
 4:   fixed_length_20  CHAR(20);
 5:   var_length_10    VARCHAR2(10);
 6:   var_length_20    VARCHAR2(20);
 7: BEGIN
 8:   --Constants are compared using blank-padded comparison semantics,
 9:   --so the trailing spaces won't affect the result.
10:   IF `Jonathan' = `Jonathan          ` THEN
11:     DBMS_OUTPUT.PUT_LINE
12:     (`Constant: `'jonathan'' = `'Jonathan          `'');
13:   END IF;
14:   --Fixed length strings are also compared with blank-padded
15:   --comparison semantic, so the fact that one is longer doesn't matter.
16:   fixed_length_10 := `Donna';
17:   fixed_length_20 := `Donna';
18:   IF fixed_length_20 = fixed_length_10 THEN
19:     DBMS_OUTPUT.PUT_LINE(`Char: `'' || fixed_length_10 || `'' =
                              Â''' || fixed_length_20 || `''');
20:   END IF;
21:   --Comparison of a fixed length string and a literal also
22:   --results in the use of blank-padded comparison semantics.
23:    IF fixed_length_10 = `Donna' THEN
24:     DBMS_OUTPUT.PUT_LINE(`Char and constant: `''
25:      || fixed_length_10 || `'' = `'' || `Donna' || `''');
26:   END IF;
27:   --But compare a variable length string
28:    --against a fixed length, and the
29:   --trailing spaces do matter.
30:   var_length_10 := `Donna';
31:   IF fixed_length_10 = var_length_10 THEN
32:     DBMS_OUTPUT.PUT_LINE(`Char and Varchar2: `''
33:       || fixed_length_10 || `'' = `''
34:       || var_length_10 || `''');
35:   ELSE
36:     DBMS_OUTPUT.PUT_LINE(`Char and Varchar2: `''
37:      || fixed_length_10 || `'' NOT = `''
38:      || var_length_10 || `''');
39:   END IF;
40:   --The maximum lengths of varchar2 strings do not matter,
41:   --only the assigned values.
42:   var_length_10 := `Donna';
43:   var_length_20 := `Donna';
44:   IF var_length_20 = var_length_10 THEN
45:     DBMS_OUTPUT.PUT_LINE(`Both Varchar2: `''

                                                   continues