Example: CASESPECIFIC Phrase
The following query returns a result only if a case specific comparison of the literal ‘Leidner P’ finds a match.
SELECT Name FROM Employee WHERE Name(CS) = 'Leidner P' ;
The literal ‘Leidner P’ might default to CS or NOT CS, depending on the current mode, but because the type modifier of the comparison is CS, the comparison is case specific irrespective of the session mode.
To ensure a comparison that is not case specific irrespective of the session mode, specify the query as follows.
SELECT Name FROM Employee WHERE Name (NOT CS) = 'Leidner P' (NOT CS) ;
Alternatively, you can specify the query using ANSI-compatible syntax as follows.
SELECT Name FROM Employee WHERE UPPER (Name) = 'LEIDNER P' ;
Example: CASESPECIFIC Comparison and Collation on Mixed Case Data
CASESPECIFIC comparison and collation on mixed case data can produce unintended results.
SELECT Last_Name FROM SalesReps ORDER BY Last_Name(CS) ;
might return one of the following sorted lists depending on the collation in effect for the session.
EBCDIC | ASCII | MULTINATIONAL |
---|---|---|
bart | ACME | ACME |
fernandez | ALBERT | Albert |
hill | Albert | ALBERT |
Albert | FARRAH | bart |
ACME | Kimble | FARRAH |
ALBERT | bart | fernandez |
FARRAH | fernandez | hill |
Kimble | hill | Kimble |