SQL2005 spaces in T-SQL

10 Dec 2008 2:34 PM

david.brunning

if I run the following statement in SQL Server Mgmt Studio:

select len(' ')

I get 0 returned. Is this expected?

10 Dec 2008 2:48 PM

Uri Dimant

David
How about
select datalength(' ')

LEN returns the number of characters, instead of the number of bytes, of the
given string expression, excluding trailing blanks.

<david.brunn***@dsl.pipex.com> wrote in message
Show quoteHide quote

news:d061d951-0cdc-4c46-b181-e5368753478d@k36g2000pri.googlegroups.com...
> if I run the following statement in SQL Server Mgmt Studio:
>
> select len(' ')
>
> I get 0 returned. Is this expected?

10 Dec 2008 5:59 PM

david.brunning

Thanks for that, interesting. I've never used datalength before, and
it returns the correct count. The reason for my initial post was that
we were doing a simple update on a table with a varchar field looking
for entries that contained a single space, this returned fields which
had empty strings also (i.e. where vch = '' and vch is not null).

I was wondering why this might be, so I guess this is the same
answer. In my select example, what would the workaround be? Should I
be searching for a character code instead?

Thanks

10 Dec 2008 7:06 PM

Plamen Ratchev

The reason for returning all values that include spaces is because SQL
Server follows the ANSI standard padding rules for character strings
used in comparisons, so that their lengths match before comparing them.
http://support.microsoft.com/default.aspx/kb/316626

As Uri noted, you can use the DATALENGTH function, using a filter
"DATALENGTH(column) = 0" will give you all rows where the column value
is a blank string.

Alternatively you can use a predicate casting the VARCHAR to VARBINARY:

WHERE column = ''
AND CAST(column AS VARBINARY(100)) = CAST('' AS VARBINARY(100));

The filter "column = ''" is added here to utilize better any index on
the column.

--
Plamen Ratchev
http://www.SQLStudio.com

12 Dec 2008 1:24 AM

Daniel Jameson

Hi,

Watch out, though,

select datalength(N' ')

will return a number twice as big as expected because there are two bytes
per character for Unicode.

--
Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Show quoteHide quote

"Plamen Ratchev" <Pla***@SQLStudio.com> wrote in message
news:RomdnR9Zb6y_jt3UnZ2dnUVZ_sLinZ2d@speakeasy.net...
> The reason for returning all values that include spaces is because SQL
> Server follows the ANSI standard padding rules for character strings used
> in comparisons, so that their lengths match before comparing them.
> http://support.microsoft.com/default.aspx/kb/316626
>
> As Uri noted, you can use the DATALENGTH function, using a filter
> "DATALENGTH(column) = 0" will give you all rows where the column value is
> a blank string.
>
> Alternatively you can use a predicate casting the VARCHAR to VARBINARY:
>
> WHERE column = ''
> AND CAST(column AS VARBINARY(100)) = CAST('' AS VARBINARY(100));
>
> The filter "column = ''" is added here to utilize better any index on the
> column.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com