When you create tables, you can set a lot of data type attribues 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.
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 comparisions.
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)
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 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.
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 or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!