«

»

Feb 23

How to get Case Sensitivity in Teradata


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

  1. Shardul

    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.

  2. zbiggy

    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’?

    1. admin

      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.

      šŸ™‚

  1. How to check CASESPECIFIC in Teradata | UsefulFreeTips.com

    […] There are also other methods of dealing with SQL tasks which involves CASESPECIFIC columns.Those methods are very easily explained in Teradatatech.com. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>