Case-conversion functions in NLSCASE INSENSITIVE databases

The UPPER and LOWER, case-conversion functions were designed to support case-insensitive queries in a case-sensitive database. They are less often needed in databases that have the NLSCASE INSENSITIVE attribute, because the NCHAR and NVARCHAR data types can support case-insensitive queries without calling these functions. You can invoke the case-conversion functions in NLSCASE INSENSITIVE databases, where their effects on CHAR, LVARCHAR, and VARCHAR data types are the same as in case-sensitive databases.

In a database created with the NLSCASE INSENSITIVE option, the database server disregards the lettercase of NCHAR and NVARCHAR values. Expressions that call case-conversion functions can return different results from what a case-sensitive database would return, if the expression references NCHAR or NVARCHAR objects, or if the database server evaluates the expression with an explicit or implicit cast to an NCHAR or NVARCHAR data type.

When the UPPER , LOWER, or INITCAP function is used in evaluating a string expression in a database with the NLSCASE INSENSITIVE property, the database server invokes the function, and applies to its return value the data-type promotion rules that are summarized in the topic Return Types from CONCAT and String Functions.
  • If the expression evaluates to a CHAR, LVARCHAR, or VARCHAR data type, the database server can use that result in case-sensitive operations, if those operations do not involve NCHAR or NVARCHAR objects.
  • If the expression evaluates to an NCHAR or NVARCHAR value after the UPPER, LOWER, or INITCAP function has executed, the case of letters in this result is disregarded in subsequent operations that use this return value from the expression.
The following example illustrates this behavior in an 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');

In the following example, the database server applies the UPPER function to the NCHAR column c1 and then applies a case-insensitive rule to return all values in that column that match the 'IBM' string constant. 

SELECT c1 FROM t1 WHERE UPPER(c1) = 'IBM';
Because NCHAR values are not case sensitive in this database, the query returns the column c1 value from every row in the table, because in each row the sequence of letters matches the string constant, using a case-insensitive rule that ignores the letter case of the column values:
c1

ibm
Ibm
IBM
The same result set (namely 'ibm', 'Ibm', and 'IBM') would be also returned by the following modifications to the same query on the same table:
  • If the projection clause specified any other column, rather than c1, because every column stores the same values, and the NCHAR value that UPPER returns makes the WHERE clause true for all lettercase variants of the string 'IBM' in this database.
  • If the 'IBM' string in the WHERE clause were any other lettercase variant of the same sequence of letters, because NCHAR data types are not processed by case-sensitive rules in this database.
  • If the NVARCHAR column c2, rather than NCHAR column c1, were the argument to the case-conversion function, because both NCHAR or NVARCHAR are case-insensitive data types in this database.
  • If the case-conversion function LOWER or INITCAP, rather than UPPER, were applied to column c1, because every (case-variant) value that NCHAR column matches 'IBM' in this database.
  • If no case-conversion function were called, but the WHERE condition instead specified c1 = 'IBM', because case-conversion functions have no effect as query filters on NCHAR or NVARCHAR arguments in this NLSCASE INSENSITIVE database.