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.

Teradata

Teradata is a company that provides data warehousing and analytic services. Teradata is a powerful solution for organisations that need to manage large amounts of data. Teradata can help organisations to make better decisions by providing insights into their data. Teradata can also help organisations to save money by reducing the need for manual data entry and data processing. Check more information about Teradata in these articles!

What means that something is Case Sensitive?

If you’re not careful, case sensitivity can trip you up when you’re programming. It’s important to be aware of whether your language is case sensitive or not, and to use the proper casing in your code.

In most languages, variable and function names are case sensitive. That means that myFunction and myfunction are two different things. If you try to call myFunction when you’ve actually defined myfunction, you’ll get an error. The same goes for variables – if you try to use myVariable when you’ve really defined MYVARIABLE, you’ll get an error.

So, how do you know whether a language is case sensitive? The best way is to consult the documentation or ask a knowledgeable friend. For example, in the C programming language, all identifiers are case sensitive, while in Java, only class names are case sensitive.

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.

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

YOU MIGHT ALSO LIKE