Problems with string value "one space"

Hello,
I've been discovering weird problems in my application, and after thorough
research, going to lower and lower levels, until I've found the following:
The setup:
MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
collation). The problems are when there is an entry with one character, code
0x20 (ASCII 32; space).
The first surprise hit me when I discovered that LEN function gives the
length of the value, NOT INCLUDING the trailing spaces. Since there is no
function that takes into account the trailing spaces, the only way to get
the length of the field seems to be something like Len(myField+'X')-1.
Awkward, if you ask me. Is there a more preferred way?
The other, which actually caused the problems in my system, was that
querying for zero-length value, it returned the record with the single
space!
SELECT * FROM MyTable WHERE myField=''
So, to avoid this, I can use the following query:
SELECT * FROM MyTable WHERE myField+'X'='X'
I assume that this behaviour might be connected with collation, but I'd hate
to select binary sort for this language-related value. What is happening
here, and how to fix it?
Thanks,
Pavils"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23SFUuWKXGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I've been discovering weird problems in my application, and after thorough
> research, going to lower and lower levels, until I've found the following:
> The setup:
> MSSQL table with nvarchar(255) field (Case-sensitive and accent-sensitive
> collation). The problems are when there is an entry with one character,
> code 0x20 (ASCII 32; space).
> The first surprise hit me when I discovered that LEN function gives the
> length of the value, NOT INCLUDING the trailing spaces. Since there is no
> function that takes into account the trailing spaces, the only way to get
> the length of the field seems to be something like Len(myField+'X')-1.
> Awkward, if you ask me. Is there a more preferred way?
>
datalength

> The other, which actually caused the problems in my system, was that
> querying for zero-length value, it returned the record with the single
> space!
> SELECT * FROM MyTable WHERE myField=''
> So, to avoid this, I can use the following query:
> SELECT * FROM MyTable WHERE myField+'X'='X'
> I assume that this behaviour might be connected with collation, but I'd
> hate to select binary sort for this language-related value. What is
> happening here, and how to fix it?
This is unrelated to collation. Trailing spaces are ignored in string
comparisons in SQL Server. To compare strings without ignoring trailing
spaces use LIKE.
EG:
select datalength('a ')
select 1 where '' = ' '
select 1 where '' like ' '
David