Search code examples
.nett-sqlcastingbinaryvarchar

Cast varchar to byte[]


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]

Solution

  • 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.