Have you observed the divergent behavior of the Teradata LIKE operator when applied to a CHAR or VARCHAR data type column? Consider the following table as an illustration:

Creating a table:

    TheVarchar VARCHAR(20),
    TheChar CHAR(20)

To illustrate, we will add a single row.

INSERT INTO TheLikeTest VALUES ('Anytext','Anytext');

We will run the SQL statement for the column with the VARCHAR data type.

SELECT TheVarchar FROM TheLikeTest WHERE TheVarchar LIKE '%t';
-> Result: 'Anytext' (1 Row)

Next, we will execute the identical query on the column with a CHAR data type.

SELECT TheChar FROM TheLikeTest WHERE TheChar LIKE '%t';
-> Result: No rows

The second query yielded no results due to a 20-space addition to the CHAR data type column. The column content contains a space character instead of the expected ‘t’ at the end.

Share your solution in the comments to prevent this undesirable behavior.

  • Newbie question,

    Wouldn’t the padding be a left padding, instead of right padding?

  • Using trim function in the where clause? Though it will increase the cost.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like