[SQL Server 2005] Mysterious problem with CASE statement

[Pradeep1210]

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

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:

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:

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:

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?

[GaryMazzone]

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.

[Pradeep1210]

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.

[Pradeep1210]

Long time no reply.

I wonder if anyone knows anything about this.

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

compare the results of

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

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

[brucevde]

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.

[Pradeep1210]

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?

No mystery there. The Len function ignores trailing spaces.

Great to know this. Thanks

[Pradeep1210]

So I now repeated the test in a different way.

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:

== 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?

[techgnome]

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

[techgnome]

And no... there is no confusion... at least from my perspective.... consider this:

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

[FunkyDexter]

I won't get into the debate of why it happens but wouldn't the simplest solution be to just LTrim the entire case:-

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.

[techgnome]

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

[FunkyDexter]

LTrim won't work... RTrim might...

Sorry, brain fart.

[techgnome]

Sorry, brain fart.

It THAT what that smell was? Man, dude... might want to do something about that... less broccoli or something. Geezes...

-tg

[Pradeep1210]

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:

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.

[techgnome]

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

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

[Pradeep1210]

ok.. So 'A' would be varchar(1), Isn't it?
So shouldn't the most flexible datatype still be Char(5)?