TrimTrailingBlanks

[hmckillop]

How do you switch this to be on.
If you do sp_help <tablename> the 8th column returned is &quot;TrimTrailingBlanks&quot;.
I want to be able to switch this to be on, how do i do this?

 

[JamesLean]

This is controlled by the setting of ANSI_PADDING when you create the table/column. By default this will be on (it is recommended this always be left on) and trailing blanks will not be trimmed.

If you did want to change it, you would have to recreate the table while SET ANSI_PADDING was off.

Look up SET ANSI_PADDING in BOL for more info, and note the recommendations and warnings.

--James

 

[Jamfool]

Below taken from help file:-

ANSI_PADDING

When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

Char and binary columns that allow nulls are padded to the length of the column when SET ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when SET ANSI_PADDING is OFF. Char and binary columns that do not allow nulls are always padded to the length of the column.



--Padding off, so TrimTrailingBlanks is set to 'yes'
set ansi_padding off
go
create table one (a varchar(50) null)
go
sp_help one
go
--Padding on, so TrimTrailingBlanks is set to 'no'
set ansi_padding on
go
alter table one alter column a varchar(50) null
go
sp_help one

 

[clarkin]

You could explicitly trim trailing blanks with RTRIM() when you insert text for the column?

INSERT INTO tableName (targetColumn) VALUES (RTRIM("my data  "))

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].