fbpx

Have you ever noticed that the Teradata LIKE operator behaves differently depending on whether you apply it to a data type CHAR or VARCHAR column? 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 behavior be avoided? Leave a comment with your solution!

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy now at Amazon
  • 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

    >