Activating Trim Trailing Blanks

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 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.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as