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

Posted on 2010-01-06
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

    LVL 41

    Accepted Solution

    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

    Thanks a lot.  That works great.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    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…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now