ADO.NET C#: varchar() fields are padded with spaces on some records

[Rene]

Hi,

I'm reading a table with varchar() strings. For some reason this fields is
sometimes padded up to the max length with spaces.

To be sure that the length is correct in the MSSQL 2000 database I added a
len(MyField) in the query.

SELECT MyField, LEN(MyField) FieldLen FROM MyTable

If I watch the contents of SqlDataReader on a 'good' record I see:

_comBuf[0] "Estna"
_comBuf[1] "5"

In case the field is padded I see:
_comBuf[0] "Kos "
_comBuf[1] "3"

So the length that SQL returns is 3, but in ADO the length is 20 (which is
in fact the maximum length).

I create a workaround to trim the field in the code, but it seems incorrect
behaviour.

Any comments?

Regards,

Rene

 

[Greg Burns]

Here is a dirty, little known secret: :^)

Try this in Query Analyzer

SELECT LEN('Estna')
returns 5

SELECT LEN('Estna ')
returns 5

SELECT LEN(' Estna')
returns 6

From BOL:

LEN
"Returns the number of characters, rather than the number of bytes, of the
given string expression, excluding trailing blanks."

HTH,
Greg