I have a table with several text fields all defined as VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC. It's my understanding that "NOT CASESPECIFIC" should mean that "WHERE product_name LIKE '%beer%'" and "WHERE product_name LIKE '%BEER%'" should return the same results.
However, I am getting different results - but only in Teradata Studio.
If I use any other client - Aqua, DBeaver, even tried Tableau and DMExpress - I get the same result set from each of these:
WHERE product_name LIKE '%beer%'
WHERE product_name LIKE '%BEER%'
WHERE product_name LIKE '%BeEr%'
However, in Teradata Studio, each WHERE clause will only return the results where the case matches.
Is there a setting somewhere that I am missing? Could this be a bug in Teradata Studio? This is using Teradata Studio Version: 15.10.00.03.201506301456. Thanks for any help!
The Studio session is probably running in ANSI mode, which defaults to case sensitivity (product_name is defined as NOT CASESPECIFIC, but the 'beer' literal is CASESPECIFIC).
Check your connection profile, Studio specifies TMODE=ANSI session mode by default, while every other connectivity (.NET/ODBC/CLI) uses the system default.
If you're used to Teradata mode you better switch it to TMODE=TERA (to make it permanent for new connections press "Save to Preference").
Otherwise change your condition to
WHERE product_name LIKE '%BEER%' (NOT CASESPECIFIC)
Thanks, dnoeth! That makes sense. I didn't know LIKE could take an argument for CASESPECIFIC, I'll pass that knowledge along.
You can apply ([NOT] CASESPECIFIC) to every comparison based on string literals/character columns.
In Teradata mode every string literal is not case sensitive by default, but in ANSI mode it's case sensitive.
And when one of the operands is case sensitive the comparison is case sensitive, so in ANSI mode you have to add it almost everywhere or you switch to Standard SQL UPPER (but this is usually less efficient).