Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Huge varbinary or image to hex string Expand / Collapse
Author
Message
Posted Wednesday, February 25, 2009 7:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Comments posted to this topic are about the item Huge varbinary or image to hex string


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #664234
Posted Monday, March 30, 2009 1:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:05 PM
Points: 1,205, Visits: 889
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

Post #685885
Posted Monday, March 30, 2009 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 14, 2010 2:51 PM
Points: 3, Visits: 15
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
...

Post #686075
Posted Monday, March 30, 2009 10:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #686301
Posted Monday, March 30, 2009 10:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #686303
Posted Monday, March 30, 2009 4:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 4:11 PM
Points: 4, Visits: 8
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]
(
@VarbinaryValue VARBINARY(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

Post #686628
Posted Wednesday, April 1, 2009 2:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Hi Robert

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

Thanks again
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #688362
Posted Wednesday, April 1, 2009 2:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #688370
Posted Wednesday, April 1, 2009 3:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #688394
Posted Thursday, January 5, 2012 4:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:31 PM
Points: 215, Visits: 843
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).
Post #1231199
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse