April 30

3 comments

Teradata LIKE – How to avoid unexpected Results!

By Roland Wenzlofsky

April 30, 2019

like operator, sql

Have you ever noticed that the Teradata LIKE operator behaves differently depending on whether you apply it to a column of data type CHAR or VARCHAR? Let us take the table below as an example:

CREATE TABLE TheLikeTest
(
TheVarchar VARCHAR(20),
TheChar CHAR(20)
);


We're inserting exactly one row for our demonstration:

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

First, we run the following SQL statement for the column of datatype VARCHAR:

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

Next, we execute the same query on the column of data type CHAR:
SELECT TheChar FROM TheLikeTest WHERE TheChar LIKE ‘%t';
-> Result: No rows returned

As we see, the second query does not return a result. How is that possible?

The solution to this puzzle is simple: Since it is a column of the data type CHAR, space characters are padded to 20 characters. Our query expects a ‘t' at the end of the column content, but there is certainly a space character there!

How can this undesired behaviour be avoided? Leave a comment with your solution!

Roland Wenzlofsky


Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

  • 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"}

    Never miss a good story!

     Subscribe to our newsletter to keep up with the latest trends!

    >