SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Florian Reischl / Huge varbinary or image to hex string / Latest Posts

RE: Huge varbinary or image to hex string

www.ssrstips.com — Fri, 03 Feb 2012 04:40:01 GMT

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.

RE: Huge varbinary or image to hex string

Florian Reischl — Thu, 02 Feb 2012 11:19:17 GMT

HiIf 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.[code="plain"] [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[i]; if (b < 0x10) { sb.Append("0"); sb.Append(b.ToString("x")); } else sb.Append(b.ToString("x")); } return new SqlString(sb.ToString()); }[/code]Hope this helpsFlo

RE: Huge varbinary or image to hex string

www.ssrstips.com — Thu, 05 Jan 2012 16:24:32 GMT

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 - @posShould be SET @offset = (@len + 1) - @posAlso, 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).

RE: Huge varbinary or image to hex string

Florian Reischl — Wed, 01 Apr 2009 15:00:55 GMT

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.GreetsFlo

RE: Huge varbinary or image to hex string

Florian Reischl — Wed, 01 Apr 2009 14:37:05 GMT

Hi cdunnThank 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!GreetsFlo

RE: Huge varbinary or image to hex string

Florian Reischl — Wed, 01 Apr 2009 14:30:18 GMT

Hi RobertI fixed the bug. I just don't know when they update it.Thanks againFlo

RE: Huge varbinary or image to hex string

cdunn-906001 — Mon, 30 Mar 2009 16:30:38 GMT

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 [url]http://conceptdevelopment.net/Database/ScriptDiagram2005/[/url] 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.[code]/** 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 longPART TWO: notices the VarbinaryValue is four bytes or less, and starts actually processing these four byte chunks. It does thisby splitting the least-significant (rightmost) byte into two hexadecimal characters and recursively calling the functionwith the more significant bytes until none remain (four recursivecalls 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 Typeshttp://msdn2.microsoft.com/en-us/library/ms178158.aspx2) Clay's "original" Script, Save, Export SQL 2000 Database Diagramshttp://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[/code]

RE: Huge varbinary or image to hex string

Florian Reischl — Mon, 30 Mar 2009 10:45:12 GMT

[quote][b]wjmsdn (3/30/2009)[/b][hr]Very nice, thanks for the articleAs an old c++ guy, i learned that declarations should be outside a controlling statement when possibleOtherwise, you are allocating and deallocating those items in every iteration[/quote]Hi wjmsdnYou are absolutely correct for C/C++! In SQL they will not be reallocated, try this:[code]DECLARE @i INTSET @i = 0WHILE (@i < 10)BEGIN DECLARE @txt VARCHAR(10) IF (@i = 0) SET @txt = 'hello world' PRINT @txt SET @i = @i + 1END[/code][b]But thank you for your hint and the feedback![/b]GreetsFlo

RE: Huge varbinary or image to hex string

Florian Reischl — Mon, 30 Mar 2009 10:42:10 GMT

[quote][b]Robert (3/30/2009)[/b][hr]A small typo in code:[/quote]Hi RobertThanks for correction!! I will fix it this evening (the article editor does not work here...).GreetsFlo

RE: Huge varbinary or image to hex string

wjmsdn — Mon, 30 Mar 2009 07:19:38 GMT

Very nice, thanks for the articleAs an old c++ guy, i learned that declarations should be outside a controlling statement when possibleOtherwise, you are allocating and deallocating those items in every iterationWHILE (@pos < @len)BEGIN DECLARE @offset INT DECLARE @sub VARCHAR(2048)Ordering like this, results in one single allocationDECLARE @offset INTDECLARE @sub VARCHAR(2048)WHILE (@pos < @len)BEGIN...

RE: Huge varbinary or image to hex string

Robert-378556 — Mon, 30 Mar 2009 01:33:01 GMT

A small typo in code:[code] IF ((@len - @pos) < 0) SET @offset = @len - @pos ELSE SET @offset = 1024[/code]Should be[code] IF ((@len - @pos) < 1024) SET @offset = @len - @pos ELSE SET @offset = 1024[/code]or[code] SET @offset = @len - @pos if @offset > 1024 SET @offset = 1024[/code]

Huge varbinary or image to hex string

Florian Reischl — Wed, 25 Feb 2009 07:27:51 GMT

Comments posted to this topic are about the item [B]Huge varbinary or image to hex string[/B]