Char column stores data with spaces but gives result even without spaces

Database
Fan

Char column stores data with spaces but gives result even without spaces

Hi ,

I am finding that a char column stores data with spaces (if all characters upto the length of the column are not given) but is able to return the row when compared without giving spaces for the column.
Please explain the behavior.

PFB the script :

Create table test_char (col1 char(3));

Insert into test_char ( col1 ) values ('%');

sel col1 from test_char where col1 = '%'; (Please note that there are no spaces here)

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

col1
----
%

sel col1 from test_char where col1 = '% '; (Please note the spaces here)

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

col1
----
%

Thanks and Regards,
Gagandeep Singh Chawla
Tags (1)
1 REPLY
Senior Apprentice

Re: Char column stores data with spaces but gives result even without spaces

Hi Gagan,
all data in a CHAR column is padded to the maximum size with blanks.

And according to Standard SQL the shorter string is padded with blanks when comparing two strings of different length.

When you actually need to store data where trailing blanks have a meaning you have to use VARCHAR and then compare using LIKE:

Create table test_char (col1 varchar(3));

Insert into test_char ( col1 ) values ('a');
Insert into test_char ( col1 ) values ('a ');
Insert into test_char ( col1 ) values ('a ');

sel col1 from test_char where col1 like 'a ';

Dieter