Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Apr 17, 2000

Activating Trim Trailing Blanks

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 ON
GO

PRINT '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 t1

if ('TEST' = 'TEST ')
	print 'EQUALS'
else
	print 'NOT EQUAL'
go
SET ANSI_PADDING OFF
go

PRINT '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 t2

go


if ('TEST' = 'TEST ')
	print 'EQUALS'
else
	print 'NOT EQUAL'
exec sp_help t1
exec sp_help t2

drop table t1
drop 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.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date