Huge varbinary or image to hex string

  • Comments posted to this topic are about the item Huge varbinary or image to hex string

  • A small typo in code:

    IF ((@len - @pos) < 0)

    SET @offset = @len - @pos

    ELSE

    SET @offset = 1024

    Should be

    IF ((@len - @pos) < 1024)

    SET @offset = @len - @pos

    ELSE

    SET @offset = 1024

    or

    SET @offset = @len - @pos

    if @offset > 1024

    SET @offset = 1024

  • Very nice, thanks for the article

    As an old c++ guy, i learned that declarations should be

    outside a controlling statement when possible

    Otherwise, you are allocating and deallocating those items in every iteration

    WHILE (@pos < @len)

    BEGIN

    DECLARE @offset INT

    DECLARE @sub VARCHAR(2048)

    Ordering like this, results in one single allocation

    DECLARE @offset INT

    DECLARE @sub VARCHAR(2048)

    WHILE (@pos < @len)

    BEGIN

    ...

  • Robert (3/30/2009)


    A small typo in code:

    Hi Robert

    Thanks for correction!! I will fix it this evening (the article editor does not work here...).

    Greets

    Flo

  • wjmsdn (3/30/2009)


    Very nice, thanks for the article

    As an old c++ guy, i learned that declarations should be

    outside a controlling statement when possible

    Otherwise, you are allocating and deallocating those items in every iteration

    Hi wjmsdn

    You are absolutely correct for C/C++! In SQL they will not be reallocated, try this:

    DECLARE @i INT

    SET @i = 0

    WHILE (@i < 10)

    BEGIN

    DECLARE @txt VARCHAR(10)

    IF (@i = 0)

    SET @txt = 'hello world'

    PRINT @txt

    SET @i = @i + 1

    END

    But thank you for your hint and the feedback!

    Greets

    Flo

  • Hi Florian,

    nice little function - I was not aware of the undocumented version (or it's 8000 limit). I have been using a similar function for a number of years now to 'script' diagrams out of SQL - the code is here http://conceptdevelopment.net/Database/ScriptDiagram2005/ for the diagramming stuff, but the basic bin-to-hex code I've included below... (yours is definitely shorter and probably faster!)

    Note I didn't write it (the sources are referenced in the header) but I did make some modifications and comments of my own. Just sharing it FYI.

    /**

    Based on ufn_VarbinaryToVarcharHex by Clay Beatty.

    Function has two 'parts':

    PART ONE: takes large VarbinaryValue chunks (greater than four bytes)

    and splits them into half, calling the function recursively with

    each half until the chunks are only four bytes long

    PART TWO: notices the VarbinaryValue is four bytes or less, and

    starts actually processing these four byte chunks. It does this

    by splitting the least-significant (rightmost) byte into two

    hexadecimal characters and recursively calling the function

    with the more significant bytes until none remain (four recursive

    calls in total).

    Clay Beatty's original function was written for Sql Server 2000.

    Sql Server 2005 introduces the VARBINARY(max) datatype which this

    function now uses.

    References

    ----------

    1) MSDN: Using Large-Value Data Types

    http://msdn2.microsoft.com/en-us/library/ms178158.aspx

    2) Clay's "original" Script, Save, Export SQL 2000 Database Diagrams

    http://www.thescripts.com/forum/thread81534.html or

    */

    CREATE FUNCTION [dbo].[Tool_VarbinaryToVarcharHex]

    (

    @VarbinaryValueVARBINARY(max)

    )

    RETURNS VARCHAR(max) AS

    BEGIN

    DECLARE @NumberOfBytes INT

    SET @NumberOfBytes = DATALENGTH(@VarbinaryValue)

    -- PART ONE --

    IF (@NumberOfBytes > 4)

    BEGIN

    DECLARE @FirstHalfNumberOfBytes INT

    DECLARE @SecondHalfNumberOfBytes INT

    SET @FirstHalfNumberOfBytes = @NumberOfBytes/2

    SET @SecondHalfNumberOfBytes = @NumberOfBytes - @FirstHalfNumberOfBytes

    RETURN dbo.Tool_VarbinaryToVarcharHex(CAST(SUBSTRING(@VarbinaryValue, 1 , @FirstHalfNumberOfBytes) AS VARBINARY(max)))

    + dbo.Tool_VarbinaryToVarcharHex(CAST(SUBSTRING(@VarbinaryValue, @FirstHalfNumberOfBytes+1 , @SecondHalfNumberOfBytes) AS VARBINARY(max)))

    END

    IF (@NumberOfBytes = 0)

    BEGIN

    RETURN ''-- No bytes found, therefore no 'hex string' is returned

    END

    -- PART TWO --

    DECLARE @LowByte INT

    DECLARE @HighByte INT

    -- @NumberOfBytes <= 4 (four or less characters/8 hex digits were input)

    -- eg. 88887777 66665555 44443333 22221111

    -- We'll process ONLY the right-most (least-significant) Byte, which consists

    -- of eight bits, or two hexadecimal values (eg. 22221111 --> XY)

    -- where XY are two hex digits [0-f]

    -- 1. Carve off the rightmost four bits/single hex digit (ie 1111)

    -- BINARY AND 15 will result in a number with maxvalue of 15

    SET @LowByte = CAST(@VarbinaryValue AS INT) & 15

    -- Now determine which ASCII char value

    SET @LowByte = CASE

    WHEN (@LowByte < 10)-- 9 or less, convert to digits [0-9]

    THEN (48 + @LowByte)-- 48 ASCII = 0 ... 57 ASCII = 9

    ELSE (87 + @LowByte)-- else 10-15, convert to chars [a-f]

    END-- (87+10)97 ASCII = a ... (87+15_102 ASCII = f

    -- 2. Carve off the rightmost eight bits/single hex digit (ie 22221111)

    -- Divide by 16 does a shift-left (now processing 2222)

    SET @HighByte = CAST(@VarbinaryValue AS INT) & 255

    SET @HighByte = (@HighByte / 16)

    -- Again determine which ASCII char value

    SET @HighByte = CASE

    WHEN (@HighByte < 10)-- 9 or less, convert to digits [0-9]

    THEN (48 + @HighByte)-- 48 ASCII = 0 ... 57 ASCII = 9

    ELSE (87 + @HighByte)-- else 10-15, convert to chars [a-f]

    END-- (87+10)97 ASCII = a ... (87+15)102 ASCII = f

    -- 3. Trim the byte (two hex values) from the right (least significant) input Binary

    -- in preparation for further parsing

    SET @VarbinaryValue = SUBSTRING(@VarbinaryValue, 1, (@NumberOfBytes-1))

    -- 4. Recursively call this method on the remaining Binary data, concatenating the two

    -- hexadecimal 'values' we just decoded as their ASCII character representation

    -- ie. we pass 88887777 66665555 44443333 back to this function, adding XY to the result string

    RETURN dbo.Tool_VarbinaryToVarcharHex(@VarbinaryValue) + CHAR(@HighByte) + CHAR(@LowByte)

    END

  • Hi Robert

    I fixed the bug. I just don't know when they update it.

    Thanks again

    Flo

  • Hi cdunn

    Thank you! Currently I plan a completely new and probably much faster version without any wrapping.

    I just needed this a possibility to format VARBINARY to hex string and I found this system function. I was a little to lazy to write an own from scratch.

    I did not try the performance of both, but the benefit of yours is it is custom and not undocumented by MS so it will definitely work with all new versions of SQL Server!

    Greets

    Flo

  • By the way...

    I just detected that the "sys.fn_varbintohexstr" in SQL Server 2008 handles VARBINARY(MAX) and VARCHAR(MAX). So this function becomes obsolete with SQL Server 2008.

    Greets

    Flo

  • I've been using this on images of about 17k in length, and it can miss the last byte - so there's a bug.

    I think, from the revised version that already includes Robert's change:

    SET @offset = @len - @pos

    Should be

    SET @offset = (@len + 1) - @pos

    Also, the built in SQL server 2008 function will hang, or appear to hang (e.g. taking 10 minutes+) on objects with a datalength of 1.5 megabytes (and likely less).

  • Hi

    If you need to convert this size of binary data I'd either try to do this in a client application (c#/java/c++/...) or consider to use a SQL CLR function.

    Just tried this one and I get a execution duration of approximately 0.5 seconds with an input buffer of 1.5 mb.

    [Microsoft.SqlServer.Server.SqlFunction]

    [return: SqlFacet(MaxSize = -1)]

    public static SqlString fn_clr_varbintohexstring(

    [SqlFacet(MaxSize=-1, IsNullable=false)]

    SqlBytes binin) {

    byte[] buffer = binin.Buffer;

    StringBuilder sb = new StringBuilder(buffer.Length * 2 + 2);

    sb.Append("0x");

    for (int i = 0; i < buffer.Length; i++) {

    byte b = buffer;

    if (b < 0x10) {

    sb.Append("0");

    sb.Append(b.ToString("x"));

    }

    else

    sb.Append(b.ToString("x"));

    }

    return new SqlString(sb.ToString());

    }

    Hope this helps

    Flo

  • Thanks for that. Though I should have come back to say I was able to optimise it by calling it with 20k chunks and putting them all back together myself... at about 1 minute per query but liveable.

    However, then I discovered that neither SSMS nor SQLCMD were built to handle rows that exceed a megabyte of raw text data. Even pulling it back as XML format (which is what Microsoft recommends in this situation) was causing issues with things being truncated... and then saving it into a text file and trying to open it in notepad or SSMS? Even more problems.

    Now I understand why stuff like SQL Data Compare (3rd party product) do it in few-thousand char wide column chunks with UPDATETEXT. Unfortunately, we don't own the scripting versions that might let me automate it.

    Anyhow I was using it in combination with another open source stored procedure that generates INSERT statements (with this plugged in to do the IMAGE bits). It'll only take a bit more pain for me to finish it up, and hopefully I can give it back without too much arm bending from the company.

  • After use this function I retrive result without 2 chars.

    When i replace code from:

    SET @len = DATALENGTH(@pbinin)

    to:

    SET @len = DATALENGTH(@pbinin)+1

    resultset contains all chars.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply