February 25, 2009 at 7:27 am
Comments posted to this topic are about the item Huge varbinary or image to hex string
March 30, 2009 at 1:33 am
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
March 30, 2009 at 7:19 am
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
...
March 30, 2009 at 10:42 am
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
March 30, 2009 at 10:45 am
wjmsdn (3/30/2009)
Very nice, thanks for the articleAs 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
March 30, 2009 at 4:30 pm
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
April 1, 2009 at 2:30 pm
Hi Robert
I fixed the bug. I just don't know when they update it.
Thanks again
Flo
April 1, 2009 at 2:37 pm
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
April 1, 2009 at 3:00 pm
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
January 5, 2012 at 4:24 pm
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).
February 2, 2012 at 11:19 am
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
February 3, 2012 at 4:40 am
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.
April 22, 2015 at 3:36 am
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