Teradata by default is case insensitive. Whatever value you compare in the WHERE clause it does not match the exact case of that value.
So what if there is a scenario when you want to compare the value along with its specific case in the WHERE clause. Here come the CASESPECIFIC keyword for the rescue.Just by adding this keyword after the value in WHERE clause we can force the Teradata to impose case sensitivity on the checking condition
Here are some example to understand it better –
1.) SELECT CURRENT_DATE
WHERE
UPPER(‘teradata’) = ‘teradata’ (CASESPECIFIC); —- This Query will not return the current_date
2.) SELECT CURRENT_DATE
WHERE
LOWER(‘teradata’) = ‘teradata’ (CASESPECIFIC) —– This query will return the current_date
3.) SELECT emp_name
FROM emp_table
WHERE emp_name (CASESPECIFIC) LIKE ‘John%’ —— This query will return all name starting with capital J only
So just by using CASESPECIFIC we can force Teradata to check for case sensitivity
3 comments
1 ping
Case specific comparison is also governed by TMODE.
ANSI mode is case sensitive while TERA mode is not, I think (or may be vice versa).
For example, in ANSI mode,
WHERE City = 'Bangalore'will be a case-specific search.In TERA mode, it will not be the case.
WHERE City = 'bangalore'will search for Bangalore in a non case-specific manner.hey,
why WHERE empname(CASESPECIFIC) LIKE ‘John%’ will return “all name starting with capital J only?”
does that mean this will return true if the empname = e.g. ‘Jordan’?
Author
Hi ,
No ‘Jordan’ will not be returned by this query because it will check for John%
what i meant to say here that –
the name (John) will be returned if its with capital J only, otherwise it will not be returned.
🙂
[…] There are also other methods of dealing with SQL tasks which involves CASESPECIFIC columns.Those methods are very easily explained in Teradatatech.com. […]