Mystery around trailing space

Database
N/A

Mystery around trailing space

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)

2 REPLIES

Re: Mystery around trailing space

Karam:

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.

Cheers.

Carlos.

N/A

Re: Mystery around trailing space

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.