ÿþ<!-- code formatted by http://manoli.net/csharpformat/ --> <style type="text/css"> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: Consolas, "Courier New", Courier, Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } </style> <div class="csharpcode"> <pre class="alt"><span class="kwrd">set</span> ANSI_NULLS <span class="kwrd">ON</span></pre> <pre><span class="kwrd">set</span> QUOTED_IDENTIFIER <span class="kwrd">ON</span></pre> <pre class="alt"><span class="kwrd">GO</span></pre> <pre><span class="rem">/**</span></pre> <pre class="alt"><span class="rem">&lt;summary&gt;</span></pre> <pre><span class="rem">Based on ufn_VarbinaryToVarcharHex by Clay Beatty.</span></pre> <pre class="alt">&nbsp;</pre> <pre><span class="rem">Function has two 'parts':</span></pre> <pre class="alt">&nbsp;</pre> <pre><span class="rem">PART ONE: takes large VarbinaryValue chunks (greater than four bytes)</span></pre> <pre class="alt"><span class="rem">and splits them into half, calling the function recursively with</span> </pre> <pre><span class="rem">each half until the chunks are only four bytes long</span></pre> <pre class="alt">&nbsp;</pre> <pre><span class="rem">PART TWO: notices the VarbinaryValue is four bytes or less, and</span> </pre> <pre class="alt"><span class="rem">starts actually processing these four byte chunks. It does this</span></pre> <pre><span class="rem">by splitting the least-significant (rightmost) byte into two </span></pre> <pre class="alt"><span class="rem">hexadecimal characters and recursively calling the function</span></pre> <pre><span class="rem">with the more significant bytes until none remain (four recursive</span></pre> <pre class="alt"><span class="rem">calls in total).</span></pre> <pre><span class="rem">&lt;/summary&gt;</span></pre> <pre class="alt"><span class="rem">&lt;author&gt;Craig Dunn&lt;/author&gt;</span></pre> <pre><span class="rem">&lt;remarks&gt;</span></pre> <pre class="alt"><span class="rem">Clay Beatty's original function was written for Sql Server 2000.</span></pre> <pre><span class="rem">Sql Server 2005 introduces the VARBINARY(max) datatype which this </span></pre> <pre class="alt"><span class="rem">function now uses.</span></pre> <pre>&nbsp;</pre> <pre class="alt"><span class="rem">References</span></pre> <pre><span class="rem">----------</span></pre> <pre class="alt"><span class="rem">1) MSDN: Using Large-Value Data Types</span></pre> <pre><span class="rem">http://msdn2.microsoft.com/en-us/library/ms178158.aspx</span></pre> <pre class="alt">&nbsp;</pre> <pre><span class="rem">2) Clay's "original" Script, Save, Export SQL 2000 Database Diagrams</span></pre> <pre class="alt"><span class="rem">http://www.thescripts.com/forum/thread81534.html or</span></pre> <pre><span class="rem">&lt;![CDATA[ http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/ca9a9229d06a56f9?dq=&amp;hl=en&amp;lr=&amp;ie=UTF-8&amp;oe=UTF-8&amp;prev=/groups%3Fdq%3D%26num%3D25%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.ms-sqlserver%26start%3D25 ]]&gt;</span></pre> <pre class="alt"><span class="rem">&lt;/remarks&gt;</span></pre> <pre><span class="rem">&lt;param name="VarbinaryValue"&gt;binary data to be converted to Hexadecimal (without leading '0x')&lt;/param&gt;</span></pre> <pre class="alt"><span class="rem">*/</span></pre> <pre><span class="kwrd">CREATE</span> <span class="kwrd">FUNCTION</span> [dbo].[Tool_VarbinaryToVarcharHex]</pre> <pre class="alt">(</pre> <pre> @VarbinaryValue VARBINARY(<span class="kwrd">max</span>)</pre> <pre class="alt">)</pre> <pre><span class="kwrd">RETURNS</span> <span class="kwrd">VARCHAR</span>(<span class="kwrd">max</span>) <span class="kwrd">AS</span></pre> <pre class="alt"> <span class="kwrd">BEGIN</span></pre> <pre> <span class="kwrd">DECLARE</span> @NumberOfBytes <span class="kwrd">INT</span></pre> <pre class="alt">&nbsp;</pre> <pre> <span class="kwrd">SET</span> @NumberOfBytes = DATALENGTH(@VarbinaryValue)</pre> <pre class="alt"> <span class="rem">-- PART ONE --</span></pre> <pre> <span class="kwrd">IF</span> (@NumberOfBytes &gt; 4)</pre> <pre class="alt"> <span class="kwrd">BEGIN</span></pre> <pre> <span class="kwrd">DECLARE</span> @FirstHalfNumberOfBytes <span class="kwrd">INT</span></pre> <pre class="alt"> <span class="kwrd">DECLARE</span> @SecondHalfNumberOfBytes <span class="kwrd">INT</span></pre> <pre> <span class="kwrd">SET</span> @FirstHalfNumberOfBytes = @NumberOfBytes/2</pre> <pre class="alt"> <span class="kwrd">SET</span> @SecondHalfNumberOfBytes = @NumberOfBytes - @FirstHalfNumberOfBytes</pre> <pre> <span class="kwrd">RETURN</span> dbo.Tool_VarbinaryToVarcharHex(<span class="kwrd">CAST</span>(<span class="kwrd">SUBSTRING</span>(@VarbinaryValue, 1 , @FirstHalfNumberOfBytes) <span class="kwrd">AS</span> VARBINARY(<span class="kwrd">max</span>)))</pre> <pre class="alt"> + dbo.Tool_VarbinaryToVarcharHex(<span class="kwrd">CAST</span>(<span class="kwrd">SUBSTRING</span>(@VarbinaryValue, @FirstHalfNumberOfBytes+1 , @SecondHalfNumberOfBytes) <span class="kwrd">AS</span> VARBINARY(<span class="kwrd">max</span>)))</pre> <pre> <span class="kwrd">END</span></pre> <pre class="alt"> </pre> <pre> <span class="kwrd">IF</span> (@NumberOfBytes = 0)</pre> <pre class="alt"> <span class="kwrd">BEGIN</span></pre> <pre> <span class="kwrd">RETURN</span> <span class="str">''</span> <span class="rem">-- No bytes found, therefore no 'hex string' is returned</span></pre> <pre class="alt"> <span class="kwrd">END</span></pre> <pre> </pre> <pre class="alt"> <span class="rem">-- PART TWO --</span></pre> <pre> <span class="kwrd">DECLARE</span> @LowByte <span class="kwrd">INT</span></pre> <pre class="alt"> <span class="kwrd">DECLARE</span> @HighByte <span class="kwrd">INT</span></pre> <pre> <span class="rem">-- @NumberOfBytes &lt;= 4 (four or less characters/8 hex digits were input)</span></pre> <pre class="alt"> <span class="rem">-- eg. 88887777 66665555 44443333 22221111</span></pre> <pre> <span class="rem">-- We'll process ONLY the right-most (least-significant) Byte, which consists</span></pre> <pre class="alt"> <span class="rem">-- of eight bits, or two hexadecimal values (eg. 22221111 --&gt; XY) </span></pre> <pre> <span class="rem">-- where XY are two hex digits [0-f]</span></pre> <pre class="alt">&nbsp;</pre> <pre> <span class="rem">-- 1. Carve off the rightmost four bits/single hex digit (ie 1111)</span></pre> <pre class="alt"> <span class="rem">-- BINARY AND 15 will result in a number with maxvalue of 15</span></pre> <pre> <span class="kwrd">SET</span> @LowByte = <span class="kwrd">CAST</span>(@VarbinaryValue <span class="kwrd">AS</span> <span class="kwrd">INT</span>) &amp; 15</pre> <pre class="alt"> <span class="rem">-- Now determine which ASCII char value</span></pre> <pre> <span class="kwrd">SET</span> @LowByte = <span class="kwrd">CASE</span> </pre> <pre class="alt"> <span class="kwrd">WHEN</span> (@LowByte &lt; 10) <span class="rem">-- 9 or less, convert to digits [0-9]</span></pre> <pre> <span class="kwrd">THEN</span> (48 + @LowByte) <span class="rem">-- 48 ASCII = 0 ... 57 ASCII = 9</span></pre> <pre class="alt"> <span class="kwrd">ELSE</span> (87 + @LowByte) <span class="rem">-- else 10-15, convert to chars [a-f]</span></pre> <pre> <span class="kwrd">END</span> <span class="rem">-- (87+10)97 ASCII = a ... (87+15_102 ASCII = f</span></pre> <pre class="alt">&nbsp;</pre> <pre> <span class="rem">-- 2. Carve off the rightmost eight bits/single hex digit (ie 22221111)</span></pre> <pre class="alt"> <span class="rem">-- Divide by 16 does a shift-left (now processing 2222)</span></pre> <pre> <span class="kwrd">SET</span> @HighByte = <span class="kwrd">CAST</span>(@VarbinaryValue <span class="kwrd">AS</span> <span class="kwrd">INT</span>) &amp; 255</pre> <pre class="alt"> <span class="kwrd">SET</span> @HighByte = (@HighByte / 16)</pre> <pre> <span class="rem">-- Again determine which ASCII char value </span></pre> <pre class="alt"> <span class="kwrd">SET</span> @HighByte = <span class="kwrd">CASE</span> </pre> <pre> <span class="kwrd">WHEN</span> (@HighByte &lt; 10) <span class="rem">-- 9 or less, convert to digits [0-9]</span></pre> <pre class="alt"> <span class="kwrd">THEN</span> (48 + @HighByte) <span class="rem">-- 48 ASCII = 0 ... 57 ASCII = 9</span></pre> <pre> <span class="kwrd">ELSE</span> (87 + @HighByte) <span class="rem">-- else 10-15, convert to chars [a-f]</span></pre> <pre class="alt"> <span class="kwrd">END</span> <span class="rem">-- (87+10)97 ASCII = a ... (87+15)102 ASCII = f</span></pre> <pre> </pre> <pre class="alt"> <span class="rem">-- 3. Trim the byte (two hex values) from the right (least significant) input Binary</span></pre> <pre> <span class="rem">-- in preparation for further parsing</span></pre> <pre class="alt"> <span class="kwrd">SET</span> @VarbinaryValue = <span class="kwrd">SUBSTRING</span>(@VarbinaryValue, 1, (@NumberOfBytes-1))</pre> <pre>&nbsp;</pre> <pre class="alt"> <span class="rem">-- 4. Recursively call this method on the remaining Binary data, concatenating the two </span></pre> <pre> <span class="rem">-- hexadecimal 'values' we just decoded as their ASCII character representation</span></pre> <pre class="alt"> <span class="rem">-- ie. we pass 88887777 66665555 44443333 back to this function, adding XY to the result string</span></pre> <pre> <span class="kwrd">RETURN</span> dbo.Tool_VarbinaryToVarcharHex(@VarbinaryValue) + <span class="kwrd">CHAR</span>(@HighByte) + <span class="kwrd">CHAR</span>(@LowByte)</pre> <pre class="alt">END</pre> </div>