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

  • Thread starter Thread starter Rene
  • Start date Start date
R

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
 
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
 
Back
Top