17.10 - Examples - Advanced SQL Engine - Teradata Database

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.