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.
| IF the session mode is … | THEN the default case specification for character columns and literals is … |
|---|---|
| ANSI | CASESPECIFIC. |
| Teradata | NOT CASESPECIFIC. The exception is character data of type GRAPHIC, which is always 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.
| IF … | THEN … |
|---|---|
| either argument has a CASESPECIFIC attribute (either by default or specified explicitly) | simple Latin letters are considered to be matching only if they are the same letters and the same case. |
| both arguments have a NOT CASESPECIFIC attribute (either by default or specified explicitly) | before the operation begins, some 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 they 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.