Update table inserting VARBINARY data

Question

When I run the sql query I got something like this :

Disallowed implicit conversion from data type varchar to data type varbinary.... Use the CONVERT function to run this query. (severity 16)`

The data I want to insert looks like

'00001200000000000010000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF...FFF'

How to done this query?

Query looks like :

UPDATE <table> SET VARBINARY_DATA = '00001200000000000010000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF....' WHERE ID = 12

Answer 1

From SQL Server 2005 onwards CONVERT does what you want:

CONVERT(varbinary(2000), '00001340132401324...', 2)

The styles for converting to/from binary are:

• 0: Raw data, ascii codepoints become binary bytes, UTF-16 codepoints become two bytes each.
• 1: Hex format, prefixed with '0x'
• 2: Hex format, not prefixed with '0x'

For converting characters to binary in format 0:

• char or varchar data (e.g. ASCII, ISO-8859-1) become binary bytes. For single character encodings this means one byte per character.
• nchar or nvarchar data (i.e. UTF-16) become two bytes each, in big-endian format, so N'ABC' becomes 0x410042004300

For converting hex to binary in formats 1 and 2:

• Each two input hex digits become one byte
• If input is not valid hex an error occurs
• Whitespace and punctuation are not allowed

See MSDN:

• http://msdn.microsoft.com/en-us/library/ms187928.aspx

If you need UTF-8 please see my answer here for a UDF which will convert text to UTF-8:

• Compute MD5 hash of a UTF8 string