Teradata: What is the difference between CASESPECIFIC and NOT CASESPECIFIC data type attributes? – you won’t believe how easy it is – 5 mins!

In this tutorial I will show you What is the difference between CASESPECIFIC and NOT CASESPECIFIC data type. When you create tables, you can set a lot of data type attributes like CHARACTER SET, FORMAT, UPPERCASE or (NOT) CASESPECIFIC. CASESPECIFIC attribute specifies case for character data comparisons and collations. What does it mean? In this post I will show you on examples what means that your columns will be case specific or not.

CASESPECIFIC and NOT CASESPECIFIC Data Type

I will create a table with four attributes: two of them will be NOT CASESPECIFIC, third will be CASESPECIFIC and the last – default. Then I will add record for my table and I will check how it works during string comparisons.

CREATE TABLE TUTORIAL.TEST (
	NAME1 		VARCHAR(100) NOT CASESPECIFIC,
	NAME2		VARCHAR(100) NOT CS,
	NAME3		VARCHAR(100) CS,
	NAME4		VARCHAR(100)
);
INSERT INTO TUTORIAL.TEST VALUES ('Test', 'Test', 'Test', 'Test');

NOT CASESPECIFIC (NOT CS) in Teradata

NOT CASESPECIFIC data type attribute in Teradata specifies that all records for this attribute will be case insensitive. What it means? For example value ‘TEST’ is completely the same like ‘test’ or ‘Test’. Let’s look!

SEL * FROM TUTORIAL.TEST WHERE NAME1 = 'TEST';

As we see on the screenshot above, Teradata found our record although we were looking for ‘TEST’, not ‘Test’ value. The same situation will be for NAME2 attributes, because NOT CS means exactly the same what NOT CASESPECIFIC.

CASESPECIFIC (CS)

CASESPECIFIC data type attribute in Teradata specifies that all records for this attribute are case sensitive. What it means? For example value ‘TEST’ is not the same like ‘Test’ what we noticed for NOT CS data type attribute. Let’s look!

SEL * FROM TUTORIAL.TEST WHERE NAME3 = 'TEST';

The above query did not return any records, but what will happen for the fourth attribute?

In my database record was return, because I use TERA Mode and the NOT CS is my default setting.

If you create a table with CHAR or VARCHAR data types, the CASESPECIFIC is the default option when you use ANSI Mode and you do not need to set it. But if you use TERA Mode, the NOT CS is the default character data.

Conclusion

To sum up:

• if you use TERA mode , case insensitive is your default setting (NOT CS)
• if you use ANSI mode, case sensitive is your deafult setting (CS)
• for NOT CS setting, ‘TEST’ means exactly the same what ‘test’, ‘Test’, ‘TeSt’ e.t.c.
• for CS setting, ‘TEST’ is not equivalent for ‘test’, ‘Test’, ‘TeSt’ e.t.c.

If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!