Data-type promotion in NLSCASE INSENSITIVE databases

In databases that have the NLSCASE INSENSITIVE property, the database server disregards the lettercase of NCHAR and NVARCHAR values. Expressions in which functions or operators avoid overflow errors by performing an implicit cast can produce different results from what a case-sensitive database would return, if the expression evaluates to an NCHAR or NVARCHAR data type.

When a string function or a string operator on which the database server supports data-type promotion returns a value that would produce an overflow error for the default VARCHAR or NVARCHAR data type of the expression, the database server performs an implicit cast on the return value, as indicated in the first table of the topic Return Types from CONCAT and String Functions:

If the none of the arguments or operands are NCHAR or NVARCHAR data types, the expression evaluates to a CHAR, LVARCHAR, or VARCHAR data type.
If any argument or operand is an NCHAR or NVARCHAR data type, the expression evaluates to an NCHAR or NVARCHAR data type.

In databases that have the NLSCASE INSENSITIVE property, operations on CHAR, LVARCHAR, or VARCHAR data types are case-sensitive, but operations on NCHAR or NVARCHAR data types are case-insensitive. Data-type promotion also produces case-insensitive results (rather than case-sensitive) from evaluating an expression that includes CHAR, LVARCHAR, or VARCHAR components, if the same expression also includes NCHAR or NVARCHAR character strings.

The following example illustrates this behavior in a NLSCASE INSENSITIVE database, in which table t1 has a character column of each of the five built-in character data types. The table stores three rows, in which each column stores the same lettercase variants of a 3-letter character string:

CREATE DATABASE db NLSCASE INSENSITIVE;
CREATE TABLE t1 (
   c1 NCHAR(20), 
   c2 NVARCHAR(20), 
   c3 CHAR((20), 
   c4 VARCHAR(20),  
   c5 LVARCHAR(20)) ;
INSERT INTO t1 values ('ibm', 'ibm', 'ibm', 'ibm', 'ibm');
INSERT INTO t1 values ('Ibm', 'Ibm', 'Ibm', 'Ibm', 'Ibm');
INSERT INTO t1 values ('IBM', 'IBM', 'IBM', 'IBM', 'IBM');

The following query retrieves the values from an NCHAR column, using an equality predicate for a literal string whose letters are all lowercase:

SELECT c1 FROM t1 WHERE c1 = 'ibm';

Because NCHAR values are not case sensitive in this database, the query returns the column c1 value from every row:

c1

ibm
Ibm
IBM

The following query on the same table returns the same case-insensitive results from CHAR column c3 that the WHERE clause casts to an NCHAR value:

SELECT c1 FROM t1 WHERE c3 = 'ibm'::NCHAR(10);

After the cast, the c3 values become case insensitive, so that every row in c3 matches the string 'ibm', and the WHERE condition is true for every row in c1 :

c1

ibm
Ibm
IBM

Because case-insensitive operations disregard differences in letter case among strings where the same letters appear in the same sequence, as in the previous example, care must be taken in databases that have the NLSCASE INSENSITIVE property to avoid contexts where data type-promotion applies case-insensitive rules to operations that you expected to be case sensitive.

See also the section Duplicate rows in NLSCASE INSENSITIVE databases.