Get bytes from a varchar
[word] is varchar(600)
The problem with the follow code is it always retrieves 30 bytes.
If [word] is longer than 30 the bytes is truncated.
If [word] is shorted than 30 then it is packed with 0s.
How can I retrieve the actual number of bytes.
Yes I really need the bytes of the [word].
Limit of 5 tags: SQL 2008 R2, C#
SqlCommand sqlCmd = sqlCon.CreateCommand();
System.Data.SqlTypes.SqlBytes charSB;
byte[] charB;
Int32 id;
sqlCmd.CommandText = "select [ID], cast(Rtrim([word]) as binary), [word] " +
"from [FTSwordDef] with (nolock) order by [word];";
SqlDataReader rdr = sqlCmd.ExecuteReader();
while (rdr.Read())
{
id = rdr.GetInt32(0);
charSB = rdr.GetSqlBytes(1);
charB = charSB.Value;
stringByte1252s.Add(new StringByte1252(id,charB));
}
rdr.Close();
This works
select top 10000 [ID], cast(Rtrim([word]) as binary(4)), len([word])
from [FTSwordDef] with (nolock) order by [word]
But I cannot pass a len([word]) as the size of the binary
Varbinary throws the same error
The following throw an error
msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
select top 10000 [ID], cast(Rtrim([word]) as binary( len([word]) )), len([word])
from [FTSwordDef] with (nolock) order by [word]
From the MS pages: http://msdn.microsoft.com/en-gb/library/ms188362(v=sql.105).aspx
"When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30."
You need to specify the length in the cast to avoid the default of 30 - if the length is not known in advance or could vary, use a varbinary with a large value for the size or even a varbinary(max) - this will also prevent unnecessary padding of the value since binary is a fixed width field.