Question:
How do I activate trim trailing blanks from No to Yes?
Answer:
The SET ANSI PADDING statement controls the setting you are referring to. When a table is being created, SQL Server checks this setting and sets the column property for each character, varchar, or varbinary field accordingly.
As an example, paste the following code into query analyzer and execute it to see the results:
SET ANSI_PADDING ONGOPRINT 'Testing with ANSI_PADDING ON'GO CREATE TABLE t1 (charnotnull char(10) not null,charcol char(10) NULL, varcharcol varchar(10) NULL )insert into t1(charnotnull, charcol,varcharcol) values ('TEST','TEST','TEST')insert into t1(charnotnull,charcol,varcharcol) values ('TEST ','TEST', 'TEST ')select *,datalength(charcol)as 'length of charchol', datalength(charnotnull)as 'length of charnotnull', datalength(varcharcol) as 'length of varcharcol'from t1if ('TEST' = 'TEST ') print 'EQUALS'else print 'NOT EQUAL'goSET ANSI_PADDING OFFgoPRINT 'Testing with ANSI_PADDING OFF'CREATE TABLE t2 (charcol char(10) NULL, charnotnull char(10)NOT NULL, varcharcol varchar(10) NULL)insert into t2(charnotnull,charcol,varcharcol) values ('TEST', 'TEST','TEST')insert into t2(charnotnull,charcol,varcharcol) values ('TEST ', 'TEST ','TEST ')select *,datalength(charcol)as 'length of charcol', datalength(charnotnull)as 'length of charnotnull', datalength(varcharcol) as 'length of varcharcol'from t2goif ('TEST' = 'TEST ') print 'EQUALS'else print 'NOT EQUAL'exec sp_help t1exec sp_help t2drop table t1drop table t2
With ANSI PADDING on, the only field that has the trailing blank trimmed is the varchar field. With the setting OFF, a char field that allows NULL is also trimmed. You will see a similar difference in the output for sp_help for each of these tables.
Note also that this affects only how the fields are stored. For comparison purposes, regardless of the settings, ‘TEST’ = ‘TEST ‘.
Additionally, the output of sp_help on T2 is a bit perplexing to me. The “trim trailing blanks” for all three says ‘yes’. But as my select statement shows, the charnotnull field does not have trailing blanks trimmed.
To get more information on this topic, look up “SET ANSI PADDING” in the books online.
