Binary Data in SQL Server 2000

[quintin]

I require to examine the bit pattern of a binary string extracted from an external device and inserted into a varbinary SQL Server table using OLE DB from Visual Basic.
Having failed to find a way to do so directly I converted the data to a HEX string, with a view to reconverting it to binary within SQL Server (which displays binary fields in Hex anyway). It seems too easy. SQL Books Online says:
"Binary constants have a leading 0x (a zero and the lowercase letter x) followed by the hexadecimal representation of the bit pattern. For example, 0x2A specifies the hexadecimal value of 2A, which is equivalent to a decimal value of 42 or a one-byte bit pattern of 00101010".
It *is* too easy. When I use the Cast or Convert functions on Hex 42 I find the value stored is 0x0000002A that is, a load of unwanted leading zeros.
How can I get a large binary string into an SQL Server database, or at least get rid of all those leading zeros??
Quintin: Desperate Developer

 

[zhavic]

Try something like SELECT CAST( 42 AS binary(1) )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[quintin]

Yes - thanks Zhavic: that appears to do exactly what I want!
Quintin: Delighted Developer