[SQL Server 2005] Mysterious problem with CASE statement
Hi,
When I perform some calcualtion in a case statement, it appends spaces randomly to the end of it. (From what I observed, it tries to make the length of the output equal to the longest possible value from all case conditions.)
sql Code:
declare @num int, @out varchar(10)
set @num = 2
set @out = case @num
when 1 then char(65)
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
The output is:
Code:
output = |AA |
length of output = 2
Notice that though the length of output is reported as 2, there are 3 spaces appended to it. So the actual length is 5.
If we repeat the same thing with constants we get the results what we expect:
sql Code:
declare @num int, @out varchar(10)
set @num = 2
set @out = case @num
when 1 then 'A'
when 2 then 'AA'
when 3 then 'AAA'
when 4 then 'AAAA'
else 'AAAAA'
end
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
The output is:
Code:
output = |AA|
length of output = 2
Notice that there are no spaces appended now. The length reported is still 2 and this is correct.
Anyone knows the reason for this behaviour and how to get past this?
Re: [SQL Server 2005] Mysterious problem with CASE statement
That is because you are using varchar as the datatype for @out. Remember when you decare the size of a varchar variable that is the max size the thing is not the actual size. The size used (lenght) will trim out the spaces on the end.
Re: [SQL Server 2005] Mysterious problem with CASE statement
The @out variable is declared as varchar(10) in both cases. The difference is only due to whether I use the constant 'A' or function char(65). When we use 'A' to generate the output, there is no space padding. But when we use char(65) to generate that A character, it pads spaces to it.
Shouldn't it behave same in both cases?
From what I observed, It makes the total length of the @out variable equal to 5 (i.e. the maximum length possible from that CASE statement). So when @num=1, it pads 4 spaces; when @num=2, it pads 3 spaces; when @num=3 it pads 2 spaces...
Mysteriously the len(@out) returns the correct length in both cases whether spaces are padded or not.
Re: [SQL Server 2005] Mysterious problem with CASE statement
Long time no reply.
I wonder if anyone knows anything about this.
Re: [SQL Server 2005] Mysterious problem with CASE statement
I figured it out.... Gary was half right... just looking at the wrong place.... and the wrong variable.
It's your case... you're returning up to 5 characters.... so think about it for a sec... what would the RETURN TYPE of the CASE be? The largest possible... a char(5) type....
At first I thought that by wrapping the entire CASE in a convert (or a CAST if you prefer) would work, but that didn't seem to do much good... but if you set each of the return values to a varchar, then it seems to work as you would expect.
compare the results of
Code:
declare @num int, @out varchar(10)
set @num = 2
set @out = case @num
when 1 then char(65)
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end)
print 'output = |' + @out + '|'
print 'output = |' + REPLACE(@out,' ','*') + '|'
print 'length of output = ' + cast(len(@out) as varchar)
to this
Code:
declare @num int, @out varchar(10)
set @num = 2
set @out = case @num
when 1 then CONVERT(varchar(5), char(65) )
when 2 then CONVERT(varchar(5), char(65) + char(65))
when 3 then CONVERT(varchar(5), char(65) + char(65) + char(65))
when 4 then CONVERT(varchar(5), char(65) + char(65) + char(65) + char(65))
else CONVERT(varchar(5), char(65) + char(65) + char(65) + char(65) + char(65))
end
print 'output = |' + @out + '|'
print 'output = |' + REPLACE(@out,' ','*') + '|'
print 'length of output = ' + cast(len(@out) as varchar)
-tg
Re: [SQL Server 2005] Mysterious problem with CASE statement
Quote:
Mysteriously the len(@out) returns the correct length in both cases whether spaces are padded or not.
No mystery there. The Len function ignores trailing spaces.
Re: [SQL Server 2005] Mysterious problem with CASE statement
Quote:
Originally Posted by
techgnome
I figured it out.... Gary was half right... just looking at the wrong place.... and the wrong variable.
It's your case... you're returning up to 5 characters.... so think about it for a sec... what would the RETURN TYPE of the CASE be? The largest possible... a char(5) type....
At first I thought that by wrapping the entire CASE in a convert (or a CAST if you prefer) would work, but that didn't seem to do much good... but if you set each of the return values to a varchar, then it seems to work as you would expect.
...
You are right. But shouldn't it behave same with the constant 'A' test?
Quote:
Originally Posted by
brucevde
No mystery there. The Len function ignores trailing spaces.
Great to know this. Thanks :thumb:
Re: [SQL Server 2005] Mysterious problem with CASE statement
So I now repeated the test in a different way.
Code:
declare @num int
declare @out varchar(10)
declare @c char(1)
declare @v varchar(1)
print '== Using CHAR function =='
set @num = 0
while (@num < 5)
begin
set @num = @num + 1
set @out = case @num
when 1 then char(65)
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
end
print ''
print '== Using a constant value =='
set @num = 0
while (@num < 5)
begin
set @num = @num + 1
set @out = case @num
when 1 then 'A'
when 2 then 'A' + 'A'
when 3 then 'A' + 'A' + 'A'
when 4 then 'A' + 'A' + 'A' + 'A'
else 'A' + 'A' + 'A' + 'A' + 'A'
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
end
print ''
print '== Using a char type variable =='
set @c = 'A'
set @num = 0
while (@num < 5)
begin
set @num = @num + 1
set @out = case @num
when 1 then @c
when 2 then @c + @c
when 3 then @c + @c + @c
when 4 then @c + @c + @c + @c
else @c + @c + @c + @c + @c
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
end
print ''
print '== Using a varchar type variable =='
set @v = 'A'
set @num = 0
while (@num < 5)
begin
set @num = @num + 1
set @out = case @num
when 1 then @v
when 2 then @v + @v
when 3 then @v + @v + @v
when 4 then @v + @v + @v + @v
else @v + @v + @v + @v + @v
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
end
Result:
Code:
== Using CHAR function ==
output = |A****|
length of output = 5
output = |AA***|
length of output = 5
output = |AAA**|
length of output = 5
output = |AAAA*|
length of output = 5
output = |AAAAA|
length of output = 5
== Using a constant value ==
output = |A|
length of output = 1
output = |AA|
length of output = 2
output = |AAA|
length of output = 3
output = |AAAA|
length of output = 4
output = |AAAAA|
length of output = 5
== Using a char type variable ==
output = |A****|
length of output = 5
output = |AA***|
length of output = 5
output = |AAA**|
length of output = 5
output = |AAAA*|
length of output = 5
output = |AAAAA|
length of output = 5
== Using a varchar type variable ==
output = |A|
length of output = 1
output = |AA|
length of output = 2
output = |AAA|
length of output = 3
output = |AAAA|
length of output = 4
output = |AAAAA|
length of output = 5
This leads us to a conclusion that the confusion is on how SQL server sees a constant string of characters.
Sometimes it treats it as CHAR type while sometimes it treats it as VARCHAR type. So with our test with constants, it returns a VARCHAR in which the trailing spaces are trimmed off. But if you simply do PRINT 'A ', it prints with spaces which means CHAR type.
I was expecting a a return type of CHAR(5) with the char function as the return type of char function is char(1).
Am I right with this?
Re: [SQL Server 2005] Mysterious problem with CASE statement
Quote:
it prints with spaces which means CHAR type.
WHACK! NO! just because it has spaces doesn't make it a char type...
if you PRINT CONVERT( char(2), 'A') .... then yes, you get the space... since you've defined it to be 2 characters long....
VarChar is like String in VB... it is a VARiable CHARacter length .... CHAR is FIXED LENGTH... Like String*5 - so when you use the type Char(5) ... you're saying. this will be 5 characters long.... no more... no less... It is a fixed size. Since you can't have an empty char string, it fills it with spaces. the Char() function explicitly returns a Char type....
I'm not sure what you were expecting with those tests you ran... they return exactly what I would expect.
-tg
Re: [SQL Server 2005] Mysterious problem with CASE statement
And no... there is no confusion... at least from my perspective.... consider this:
Code:
Dim myInt as Int32
Dim myshort as Int16
Dim myLong as Int64
Dim myDecimal as Decimal
myInt = 100
myLong = 100
myShort = 100
myDecimal = 100
Are they not the same thing? even though 100 is a "constant" it gets typed to the proper type during the assignment. We do stuff like this all the time, and it doesn't cause confusion...
-tg
Re: [SQL Server 2005] Mysterious problem with CASE statement
I won't get into the debate of why it happens but wouldn't the simplest solution be to just LTrim the entire case:-
Code:
SET @out = LTrim(case @num
when 1 then char(65)
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end)
I haven't got SQLServer in front of me to trythis but it should get round the issue.
Re: [SQL Server 2005] Mysterious problem with CASE statement
the spaces aren't on the left, so LTrim won't work... RTrim might... but I think you'd still run into the problem as it's going to return a CHAR(5) .... worth a try though.
-tg
Re: [SQL Server 2005] Mysterious problem with CASE statement
Quote:
LTrim won't work... RTrim might...
:blush:Sorry, brain fart.
Re: [SQL Server 2005] Mysterious problem with CASE statement
Quote:
Originally Posted by
FunkyDexter
:blush:Sorry, brain fart.
It THAT what that smell was? Man, dude... might want to do something about that... less broccoli or something. Geezes... ;)
-tg
Re: [SQL Server 2005] Mysterious problem with CASE statement
Quote:
Originally Posted by
techgnome
And no... there is no confusion... at least from my perspective.... consider this:
...
Are they not the same thing? even though 100 is a "constant" it gets typed to the proper type during the assignment. We do stuff like this all the time, and it doesn't cause confusion...
-tg
The confusion is: It behaves differently if there is atleast one case with a constant value. This is usually the case when we are actually using it in stored procedures etc.
Consider this:
Code:
declare @num int
declare @out varchar(10)
set @num = 3
set @out = case @num
when 1 then char(65)
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
set @out = case @num
when 1 then 'A'
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
Technically the output of these two should be same as we are not touching case 1 in either the first one or the second one. Yet output is different, based on whether at-least one of the cases is a constant value or not.
Re: [SQL Server 2005] Mysterious problem with CASE statement
It's going to go with the most flexible type.... case your 'A' to a Char(1) ... you should then get the same results again...
Code:
declare @num int
declare @out varchar(10)
set @num = 3
set @out = case @num
when 1 then char(65)
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
set @out = case @num
when 1 then 'A'
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
set @out = case @num
when 1 then CAST('A' as char(1))
when 2 then char(65) + char(65)
when 3 then char(65) + char(65) + char(65)
when 4 then char(65) + char(65) + char(65) + char(65)
else char(65) + char(65) + char(65) + char(65) + char(65)
end
set @out = replace(@out,' ','*')
print 'output = |' + @out + '|'
print 'length of output = ' + cast(len(@out) as varchar)
Results:
output = |AAA**|
length of output = 5
output = |AAA|
length of output = 3
output = |AAA**|
length of output = 5
since a varchar is more flexible than a char, then that's what it goes with in that second case.
In the first and third example, the largest thing that can come out of it is a Char(5), so that's what gets returned.
It's almost a shame that SQL doesn't have a TypeOf operator...
-tg
Re: [SQL Server 2005] Mysterious problem with CASE statement
ok.. So 'A' would be varchar(1), Isn't it?
So shouldn't the most flexible datatype still be Char(5)?
