Monday, October 17, 2005
Space matters
Further to my blog post "T-SQL funs from community sites", I would like to add some points on that.
SQL Server ignores the trail space in the strings when comparing two string values (char and varchar values). We can check the behavior through this code.
----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)
set @myStr = 'String'
set @urStr = 'String '
select len(@myStr) as [MyString Length], len(@urStr) as [UrString Length]
----------------End of the script ------------
The above code will return length 6 for both the strings. Further more, the following code returns “Equal” even though the variable @urStr contains the trail space in it.
----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)
set @myStr = 'String'
set @urStr = 'String '
if @myStr = @urStr
print 'Equal'
else
print 'Not Equal'
----------------End of the script ------------
If we are concerned about the trail space and want to know accurate length, DATALENGTH() function comes handy. This function won’t ignore the trail space. This updated code will demonstrate that.
----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)
set @myStr = 'String'
set @urStr = 'String '
select datalength(@myStr) as [MyString Length], datalength(@urStr) as [UrString Length]
if @myStr = @urStr and datalength(@myStr) = datalength(@urStr)
select 'Equal'
else
select 'Not Equal'
----------------End of the script ------------
Now the code returns accurate length and comparision. :)
SQL Server ignores the trail space in the strings when comparing two string values (char and varchar values). We can check the behavior through this code.
----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)
set @myStr = 'String'
set @urStr = 'String '
select len(@myStr) as [MyString Length], len(@urStr) as [UrString Length]
----------------End of the script ------------
The above code will return length 6 for both the strings. Further more, the following code returns “Equal” even though the variable @urStr contains the trail space in it.
----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)
set @myStr = 'String'
set @urStr = 'String '
if @myStr = @urStr
print 'Equal'
else
print 'Not Equal'
----------------End of the script ------------
If we are concerned about the trail space and want to know accurate length, DATALENGTH() function comes handy. This function won’t ignore the trail space. This updated code will demonstrate that.
----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)
set @myStr = 'String'
set @urStr = 'String '
select datalength(@myStr) as [MyString Length], datalength(@urStr) as [UrString Length]
if @myStr = @urStr and datalength(@myStr) = datalength(@urStr)
select 'Equal'
else
select 'Not Equal'
----------------End of the script ------------
Now the code returns accurate length and comparision. :)