17.10 - Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

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