Is it normal that varchar variable is automatically trimmed in t-sql ?

Question

Is it normal that varchar variable is automatically trimmed in t-sql ?

John S-1137548

Grasshopper

Points: 21
More actions
August 3, 2009 at 10:23 am

#133685

-- In this example I added ANSI_PADDING on and off just to make sure that this is not ANSI_PADDED
-- related and I did not think so.
--
-- As you will see : 'a' (1 character) is equal to 'a ' ( 2 characters : a plus a space )
--
set ansi_padding on
go
declare @v1 varchar(5)
declare @v2 varchar(5)
set @v1 = 'a'
set @v2 = 'a '
print 'ANSI_PADDING = ' + cast(sessionproperty('ANSI_PADDING') as char(1))
print '@v1 = |' + @v1 + '|'
print '@v2 = |' + @v2 + '|'
if @v1 = @v2 print '|' + @v1 + '|' + ' = ' + '|' + @v2 + '|'
print ''
print '===================================================='
print ''
go
set ansi_padding off
go
declare @v1 varchar(5)
declare @v2 varchar(5)
set @v1 = 'a'
set @v2 = 'a '
print 'ANSI_PADDING = ' + cast(sessionproperty('ANSI_PADDING') as char(1))
print '@v1 = |' + @v1 + '|'
print '@v2 = |' + @v2 + '|'
if @v1 = @v2 print '|' + @v1 + '|' + ' = ' + '|' + @v2 + '|'
--
-- And here is the output of the execution
Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86)
Dec 17 2008 15:19:45
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 rows affected)
ANSI_PADDING = 1
@v1 = |a|
@v2 = |a |
|a| = |a |
====================================================
1> go
ANSI_PADDING = 0
@v1 = |a|
@v2 = |a |
|a| = |a |
1>

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15256 More actions · Answer 1

This is one of those kind of weird things that you just have to keep in mind... ansi_padding is an option that controls this behavior, but it works a bit differently for variables.

So here's the code that demonstrates how ansi_padding changes how trailing blanks are dealt with:

set ansi_padding on

go

create table #t1 (col1 varchar(5))

create table #t2 (col1 varchar(5))

insert #t1 select 'a'

insert #t2 select 'a '

print 'ANSI_PADDING = ' + cast(sessionproperty('ANSI_PADDING') as char(1))

select '|' + col1 + '|' from #t1

select '|' + col1 + '|' from #t2

drop table #t1

drop table #t2

go

set ansi_padding off

go

create table #t1 (col1 varchar(5))

create table #t2 (col1 varchar(5))

insert #t1 select 'a'

insert #t2 select 'a '

print 'ANSI_PADDING = ' + cast(sessionproperty('ANSI_PADDING') as char(1))

select '|' + col1 + '|' from #t1

select '|' + col1 + '|' from #t2

drop table #t1

drop table #t2

However, when it comes to variables, it doesn't seem to make any difference either way and I've not been able to figure out why or how to change it. So, if you want to eliminate the trailing blanks you'll have to use RTRIM

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 2

Yes, it is normal. Unlike char, the varchar datatype is variable in length and no purpose would be served by storing trailing blanks.

From BOL (books online):

char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

Do you want to have strings with different amounts of trailing blanks? If so, why?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

I'll also add that you should propably get used to this because SET ANSI_PADDING OFF has been deprecated as have the "off" conditions for many ANSI settings have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

John S-1137548 Grasshopper Points: 21 More actions · Answer 4

Thank you for all your replies.

Actually my question was not posted properly, it is actually : Is it normal that varchar variable is trimmed automatically in comparison ( or in a IF statement like in this case ) ?

The print statement shows that @v2 has an trailing space, however the IF statement

does not "see" it. This looks like a bug to me.

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 5

It's not a bug. That behavior is consistent even with char data (see code below).

Trailing blanks or spaces are disregarded, and in my opinion that's a fine thing too. Otherwise string comparisons would have to be accompanied by length comparisons or padding.

I'm curious: Why do you care about the number of trailing blanks?

declare @char char(100)

set @char = 'Test ' -- trailing spaces added

select LEN(@char), case when @char = 'Test' then 'Match' else 'No Match' end

if @char = 'Test'

begin

select 'The spaces were trimmed.'

end

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

DCPeterson SSCoach Points: 15256 More actions · Answer 6

No, according to the ANSI standard trailing blanks are never ignored for comparison. And as you found out, even turning the ANSI_Padding off doesn't affect how variable values are treated. I've always looked at this kind of like implicit conversion of data types, if you need it done, do it yourself explicitly rather than rely on some setting that could cause your code to break.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/