Rules for Character Type Arguments - Teradata VantageCloud Lake

If the arguments are character types, matching is in terms of logical characters. Single byte characters are matched against single byte characters, and multibyte characters are matched against multibyte characters. For a match to occur, representation of the logical character must be identical in both expressions.

If the server character sets of the arguments are not the same, INDEX performs an implicit character translation.

The CASESPECIFIC attribute affects whether characters are considered to be a match.

Session Mode	Default case specification for character columns and literals
ANSI	CASESPECIFIC.
Teradata	NOT CASESPECIFIC. The exception is character data of type GRAPHIC, which is CASESPECIFIC.

To override the default case specification, you can apply the CASESPECIFIC or NOT CASESPECIFIC phrase to a character column in CREATE TABLE or ALTER TABLE.

Or, you can apply the CASESPECIFIC or NOT CASESPECIFIC phrase to the INDEX character string arguments.

Situation	Effect
Either argument has a CASESPECIFIC attribute (either by default or specified explicitly)	Matching Latin letters are the same letters in the same case.
Both arguments have a NOT CASESPECIFIC attribute (either by default or specified explicitly)	Before the operation begins, lowercase characters are converted to uppercase. If the character is a lowercase simple Latin letter, the character is converted to uppercase before the operation begins. If the character is a non-Latin single byte character, a multibyte character, or a byte indicating a transition between single-byte and multibyte character data, the character is not converted to uppercase.

Situation

Effect

Either argument has a CASESPECIFIC attribute (either by default or specified explicitly)

Matching Latin letters are the same letters in the same case.

Both arguments have a NOT CASESPECIFIC attribute (either by default or specified explicitly)

Before the operation begins, lowercase characters are converted to uppercase.

If the character is a lowercase simple Latin letter, the character is converted to uppercase before the operation begins.

If the character is a non-Latin single byte character, a multibyte character, or a byte indicating a transition between single-byte and multibyte character data, the character is not converted to uppercase.

Using the rules for character type arguments, if you want INDEX to match letters only if the letters are the same letters in the same case, specify the CASESPECIFIC phrase with at least one of the arguments. For example:

   SELECT Name 
   FROM Employee 
   WHERE INDEX(Name, 'X' (CASESPECIFIC)) = 1;

If you want INDEX to match letters without considering the case, specify the NOT CASESPECIFIC phrase with both of the arguments.