[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How can a parse a varbinary(max) field that contains an array of Int in hex value?

Posted on 2010-01-06
Medium Priority
Last Modified: 2012-05-08
I have a varbinary(max) column in my SQL Server 2005 table that has an array of Int values stored in it.  

Example when displayed via sys.fn_varbintohexsubstring function:
0x0000006d0000002b00000045 which when converted to Int is
0000006d = 109
0000002b = 43
00000045 = 69

I need to take the column, parse it and convert it to Int values for updating another table.  Can someone point me in the right direction?   I assume that using the Substring function would work, then using the Convert function, but I have not been able to get it working correctly.  I am new to T-SQL and SQL Server and have been banging my head against the computer trying to get the parsing to work correctly.  
Question by:daileyan
LVL 11

Expert Comment

ID: 26191258
LVL 43

Accepted Solution

pcelba earned 1000 total points
ID: 26191325
SELECT CONVERT(int, SUBSTRING(vbmax,1,4))  Col1, CONVERT(int, SUBSTRING(vbmax,5,4))  Col2,
     CONVERT(int, SUBSTRING(vbmax,9,4))  Col3, CONVERT(int, SUBSTRING(vbmax,13,4))  Col4
  FROM YourTable

Author Closing Comment

ID: 31673507
Thanks a lot.  That works great.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question