Wednesday, January 1, 2014

Oracle String Comparison

There are two basic rules used compare character values in Oracle.

Blank-padded Comparison
Blank-padded comparison is used with CHAR, NCHAR, text literals or values returned by the USER function.  Here if two values have different lengths, then blanks are added to the shorter one such that their lengths are equal.  Then values are compared character by character up to the first character that differs. The value with the greater character at the first differing position is considered as greater.

Non-padded Comparison

This is used with VARCHAR2 or NVARCHAR2. Here no padding is used. Instead oracle compares two values character by character up to the first character that differs.  The value with the greater character in that position is considered greater. If the lengths of the two comparing values are different and the values are identical up to the end of the shorter longer value is considered greater.

Here I have mentioned a small example on how blank-padded comparison and non-padded comparison will behave when comparing two string values.

Comparing 'a ' and 'a'

'a ' = 'a' is TRUE with blank-padded comparison 


 but with non-padded comparison it is FALSE




 'a ' > 'a' is FALSE with blank-padded comparison


 but with non-padded comparison it is TRUE


No comments:

Post a Comment