Comparing Character Values
You can use the relational operators to compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. One character value is greater than another if it follows it in the collating sequence. For example, given the following declarations , the IF condition is TRUE.
The SQL standard requires that two character values being compared have equal lengths. If both values in a comparison have datatype CHAR, blank-padding semantics are used. Before comparing character values of unequal length, PL/SQL blank-pads the shorter value to the length of the longer value. For example, given the following declarations, the IF condition is TRUE.
If either value in a comparison has datatype VARCHAR2, non-blank-padding semantics are used: when comparing character values of unequal length, PL/SQL makes no adjustments and uses the exact lengths. For example, given the following declarations, the IF condition is FALSE.
If a VARCHAR2 value is compared to a CHAR value, non-blank-padding semantics are used. But, remember, when you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Given the following declarations, the IF condition is FALSE because the value of last_name2 includes five trailing blanks.
All string literals have datatype CHAR. If both values in a comparison are literals,
blank-padding semantics are used. If one value is a literal, blank-padding semantics
are used only if the other value has datatype CHAR.
DECLARE
last_name1 VARCHAR2(10) := 'COLES';
last_name2 VARCHAR2(10) := 'COLEMAN';
BEGIN
IF last_name1 > last_name2 THEN
dbms_output.Put_line(last_name1
||' is greater than '
||last_name2);
ELSE
dbms_output.Put_line(last_name2
||' is greater than '
||last_name1);
END IF;
END;
/
The SQL standard requires that two character values being compared have equal lengths. If both values in a comparison have datatype CHAR, blank-padding semantics are used. Before comparing character values of unequal length, PL/SQL blank-pads the shorter value to the length of the longer value. For example, given the following declarations, the IF condition is TRUE.
DECLARE
last_name1 CHAR(5) := 'BELLO';
last_name2 CHAR(10) := 'BELLO '; -- note trailing blanks
BEGIN
IF last_name1 = last_name2 THEN
dbms_output.Put_line(last_name1
||' is equal to '
||last_name2);
ELSE
dbms_output.Put_line(last_name2
||' is not equal to '
||last_name1);
END IF;
END;
/
If either value in a comparison has datatype VARCHAR2, non-blank-padding semantics are used: when comparing character values of unequal length, PL/SQL makes no adjustments and uses the exact lengths. For example, given the following declarations, the IF condition is FALSE.
DECLARE
last_name1 VARCHAR2(10) := 'DOW';
last_name2 VARCHAR2(10) := 'DOW '; -- note trailing blanks
BEGIN
IF last_name1 = last_name2 THEN
dbms_output.Put_line(last_name1
||' is equal to '
||last_name2);
ELSE
dbms_output.Put_line(last_name2
||' is not equal to '
||last_name1);
END IF;
END;
/
If a VARCHAR2 value is compared to a CHAR value, non-blank-padding semantics are used. But, remember, when you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Given the following declarations, the IF condition is FALSE because the value of last_name2 includes five trailing blanks.
DECLARE
last_name1 VARCHAR2(10) := 'STAUB';
last_name2 CHAR(10) := 'STAUB'; -- PL/SQL blank-pads value
BEGIN
IF last_name1 = last_name2 THEN
dbms_output.Put_line(last_name1
||' is equal to '
||last_name2);
ELSE
dbms_output.Put_line(last_name2
||' is not equal to '
||last_name1);
END IF;
END;
/
All string literals have datatype CHAR. If both values in a comparison are literals,
blank-padding semantics are used. If one value is a literal, blank-padding semantics
are used only if the other value has datatype CHAR.
Posts
0 comments:
Post a Comment