On running below queries ,why i'm getting the same result despite the fact that the 2nd query is having trailing spaces:
1st query with no leading or trailing space
SELECT * FROM DBC.ALLROLERIGHTS WHERE ROLENAME = 'ETL_ROLE';
2nd query with trailing space
SELECT * FROM DBC.ALLROLERIGHTS WHERE ROLENAME = 'ETL_ROLE ';
The datatype of column ROLENAME is VARCHAR(128)
There is no 'mystery' in that. It's only ANSI SQL. Strings are padded with spaces to match the lengths before comparision.
The exception is 'LIKE'. See it yourself: replace '=' with LIKE in your queries.
Yes..Thanks Carlos..that helps...
I dig further and heres my finding:
Teradata follows the ANSI standard on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other string comparisons.
For example, Teradata considers the strings ‘xyz’ and ‘xyz ‘ to be equivalent for most comparison operations with the exception while using LIKE clause.