Home > SQL Server > SQL SERVER – SET ANSI_PADDING – SET Statement

SQL SERVER – SET ANSI_PADDING – SET Statement


ANSI_PADDING is used to control the tailing white spaces and zeros stored in the char, varchar, binary and varbinary data typed column. This set statement is used to trim or add white spaces or zeros at the end of the character and binary data stored in a column.

Note: In the future version of SQL Server (SQL SERVER 2014) ANSI_PADDING will be ON and we cannot turn it OFF.

Usage

SET ANSI_PADDING ON

When ANSI_PADDING is ON and

If we use the char data type, it adds white space at the end of the string if the length of the value is less than the column size.

If we use binary data type, it adds zero at the end of the binary value if the length of the value is less than the column size.

Example for ANSI_PADDING ON

1. Handling char and binary columns in a table

SQL Query

SET ANSI_PADDING ON

Drop table tblpadd
GO

create table tblPadd
(
CHAR_name1 char(5),
BINARY_name1 binary(5)
)

insert into tblpadd (CHAR_name1, BINARY_name1) values(‘AB’, 0x2)
GO

Select CHAR_name1, BINARY_name1 from tblpadd
GO

Output

pic1If the data types are varchar and varbinary and ANSI_PADDING is ON than it does not remove tailing blank spaces in the varchar column and tailing zeros in varbinary column.

pic2

SET ANSI_PADDING OFF

When ANSI_PADDING is OFF and the data types are char and binary than it adds tailing space to the character column and tailing zeros to the binary column.

But, when ANSI_PADDING is OFF and the data types are varchar and varbinary than it removes the tailing white space from the varchar column and removes tailing zeros from the varbinary column.

Example for ANSI_PADDING OFF

1. Using varchar and varbinary column to store values with tailing space and tailing zeros.

pic3we can see it the output that SQL Server removed tailing space from the varchar column and tailing zeros from the varbinary column.

I hope you have understood the concept and the use of ANSI_PADDING set statement. Happy coding!

 

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: