Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update table inserting VARBINARY data

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
like image 397
John Avatar asked Jan 26 '12 16:01

John


People also ask

What datatype is VARBINARY?

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes. It contains no character set, and comparison and sorting are based on the numeric value of the bytes.

How do you update data in an existing table?

To update data in a table, you need to: First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,).

Can we convert VARCHAR to VARBINARY in SQL Server?

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query. The problem is that the query plan hashes are already in binary format, however stored as VARCHAR in the XML Query Plan e.g.


1 Answers

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
like image 135
Ben Avatar answered Sep 19 '22 17:09

Ben