How to set varbinary column in SQL server

Question

I am trying to update column of type VARBINARY(MAX) by following SQL:

UPDATE dbo.__MigrationHistory 
SET Model = 0x1F8B080000000000046
WHERE MigrationId = '201510051415248_Test2'

And I am expecting that column to be set to 0x1F8B080000000000046 but it set to 0x01F8B080000000000046

How I can set Model to desired value: 0x1F8B080000000000046?

Thanks

Update 1.

0x1F8B080000000000046 is just a random value that I managed to replicate issue with. I didn't put original value, because I it consists of 43679 symbols. And MOST confusing thing is that same value is already in database and I am copying that value from another row and pasting it into my update query, however it will work if update it from select e.g.:

UPDATE dbo.__MigrationHistory
SET Model = (
        SELECT Model
        FROM dbo.__MigrationHistory
        WHERE MigrationId = '201510041415248_Test1'
        ) -- is equal to 0x1F8B080000000000046
WHERE MigrationId = '201510051415248_Test2'

But obviously I cannot use that approach on database where there is no that value initially.

So you want to set its length to a fractional number of bytes? — , Oct 09 '15 at 16:09
@hvd I just want 0x1F8B080000000000046 value appear in my table in Model column — Vladimirs, Oct 09 '15 at 16:10
@AlexK. Surely irrelevant? But what if value that I am updating has MAX length and because of that extra leading 0 last character is trimmed out and I get invalid varbinary? — Vladimirs, Oct 09 '15 at 16:14
@Vladimirs: You can only store values with whole bytes, so what you are asking for is impossible. So the question is, why do you want to set that specific value? — Guffa, Oct 09 '15 at 16:50
@Guffa what if I say, that I am copying that value from another row (see update 1) — Vladimirs, Oct 12 '15 at 10:36
@Vladimirs: A varbinary literal doesn't have 43679 characters, it has to be an even number. It's likely that you are getting a concatenated value when you copy it. — Guffa, Oct 12 '15 at 10:47
@Guffa you are totally right, I tried `LEN(CONVERT(varchar(max),Model,1))` and it returned 85188. Appears to be a known issue https://connect.microsoft.com/SQLServer/feedback/details/499608/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode but saving as CSV works fine. Do you want to format it as a proper answer so I can accept it? — Vladimirs, Oct 12 '15 at 11:13

score 3 · Answer 1 · answered Oct 09 '15 at 16:28

varbinary values displayed with 0x at the beginning are in hexadecimal format.

In hexadecimal format, values MUST consist of an even number of digits.

SQL Server is attempting to set the value to 0x1F8B080000000000046, however since that has an odd number of digits, SQL Server prepends the extra 0 to the beginning to make the value a valid hexadecimal value.

Without having any knowledge of your data, I'd say you probably mean to set it to: 0x1F8B0800000000000046.

Are you attempting to append the 46 to 0x1F8B08? If so, you must ensure you add the correct number of zeros to end up with an even number of digits.

Thanks for your explanation, the original value indeed has an odd number of digits (43677 excluding 0x), but this is value that I am seeing in database already - issue is that I cannot use it as a value (see update 1). — Vladimirs, Oct 12 '15 at 10:41

score 1 · Accepted Answer · answered Oct 12 '15 at 14:35

A varbinary isn't displayed with an odd number of digits. The value contains a specific number of bytes, and each byte is displayed as two digits.

(You can write a binary literal with an odd number of digits, for example 0x123, but then it means the same things as 0x0123.)

As you copy a value that has 43679 digits, it's not a correct value from the database. Most likely it's because it gets concatenated, either when it is displayed or when you copy it.

How to set varbinary column in SQL server

2 Answers2