Wednesday, March 4, 2009

I don't care what they say... 6 != 4

When writing a SQL query today (against SQL Server 2005) I came to realize I can't trust the len function. Well, ok, I can trust it but not if the value being evaluated has trailing white space. Take this code:

Declare @value char(10)
Set @value = ' 123 '
Print Len( @value)

Notice I set @value to ' 123 ', that's [space][1][2][3][space][space]. Now even if I count like my 6 year old that's 6 characters. But what does the code above say? 4. Four?!?!? Lies!!!

And it lies to me if I declare @value as char, nchar, varchar and nvarchar data type. I can see varchar because it's a variable length data type. However char is a fixed length data type so I was expecting either a length of 6 (I did, after all, assign a string literal with 6 characters) or 10 because @value was declared as a char(10).

So I don't know what to believe anymore.