Case Sensitivity of Data Teradata

It has been discussed earlier that there is no need for concern regarding the use of lower or upper case characters when coding the SQL. As a matter of fact, the different case letters can be mixed in a single statement. Normally, the Teradata database does not care about the case when comparing the stored data either.

However, the ANSI mode implementation of the Teradata RDBMS is case sensitive, regarding the data. This means that it knows the difference between a lower case letter like ‘a’ and an upper case letter ‘A’. At the same time, when using Teradata mode within the Teradata database, it does not distinguish between upper and lower case letters. It is the mode of the session that dictates the case sensitivity of the data.

The SQL can always execute ANSI standard commands in Teradata mode and likewise, can always execute Teradata extensions in ANSI mode. The SQL is always the same regardless of the mode being used. The difference comes when comparing the results of the data rows being returned based on the mode.

For example, earlier in this chapter, it was stated that ANSI mode does not allow truncation. Therefore, the FORMAT could be used in either mode because it did not truncate data.

To demonstrate this issue, the following uses the different modes in BTEQ:

No Rows Returned

The above SQL execution is case specific due ANSI mode and ‘A’ is different than ‘a’. The same SQL is executed again here, however, the transaction mode for the session is set to Teradata mode (BTET) prior to the logon:

1 Row Returned
They match

Now that the defaults have been demonstrated, the following functions can be used to mimic the operation of each mode while executing in the other (ANSI vs Teradata) where case sensitivity is concerned

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics