BinToString

				
--**************************************
-- Name: BinToString
-- Description:The purpose of this SQL function is to convert a binary value to a formatted string.
-- By: Bruce Donohue
--
-- Inputs:Accepts a single input, @bX, which can be a BINARY, VARBINARY, or IMAGE datatype.
--
-- Returns:Returns a formatted varchar(6600) containing the converted binary value.
--
-- Assumes:This, as written, will run only on MS SQL 2000.
--
-- Side Effects:The maximum conversion size with this code is 3298 ((n/2)-2, where n=size of the output varchar, 6600).
I primarially use this function to convert binary 512 byte encrypted chunks to text readable 1026 (1024 + 2 char header) strings for outputting to binary character unfriendly systems, like email.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=595&lngWId=5--for details.--**************************************

set nocount on
/*-------------------------------------------------------------------------------
Hex to String Converter - A MS SQL 2000 Function
--The purpose of this function is to provide a method of handling binary
--values as strings. This is useful when dealing with external systems that expect
--data as text readable hex, such as encryption systems.
--Inputs: BinToString (@bX image)
--This function will accept binary, varbinary, or image as an input.
--Returns a varchar(6600)
--Long binary inputs of 3298 ((n/2)-2 length) will be truncated by this code.
--Bruce Donohue, November 2002.
--weasel@batzx.com
--Free code, do with what you will. 
--Enjoy.
-------------------------------------------------------------------------------*/
go
/*-----------------------------------------------------------------------------------
--Binary/Varbinary/Image to Varchar converter for those annoying formatting problems
-----------------------------------------------------------------------------------*/
if exists (select * from sysobjects where id = object_id('dbo.BinToString') )
	drop function dbo.BinToString
go
create function dbo.BinToString (@bX image)
returns	varchar(6600)
as
	begin
	--Declare local variables
	declare @sHex 	varchar(20),
		@nZloop	int,
		@nMax	int,
		@nVal	int,
		@sOut	varchar(6600)
	--Initialize the local variables
	set	@sHex = '0123456789ABCDEF' 	--The character ordinality determines the hexadecimal equivalent.
	set	@sOut = '0x'			--Initialize the output with the standard hex notation '0x'
	set	@nMax = datalength(@bX)		--Get the sizeof the input binary
	set	@nZloop = 1			--Initialize the loop variable
	if @nMax > 6600				--Check to make sure the loop stays with in bounds.
		set @nMax = 6600
	while @nZloop <= @nMax			--Begin stepping through the binary data
		begin
		--convert the byte at array element @nZloop to an int
		set @nVal = convert(int,substring(@bX,@nZloop,1))	
		--though it should be impossible for the byte to be greater than 255, check it anyway.
		if @nVal between 0 and 255
			begin
			--Convert to two hex characters for each int. 
			--If you had a value of 213 the hex value is D5, where D is the high byte, 5 is the low.
			--substring(@sHex,((@nVal -(@nVal % 16))/16)+1,1) determines the 'high' byte
			--substring(@sHex,(@nVal % 16) + 1,1) determines the 'low' byte
			set	@sOut = @sOut +
				 case (@nVal -(@nVal % 16))
					when 0 then '0'
					else substring(@sHex,((@nVal -(@nVal % 16))/16)+1,1) 
				end +
				substring(@sHex,(@nVal % 16) + 1,1)
			end
		--increment the loop
		set @nZloop = @nZloop + 1
		end
	--output the final results
	return @sOut
	end 
go
	
/*--------------------------------------------------------------------------------
--example of usage
----------------------------------------------------------------------------------
Example One: binary to formatted varchar conversion
declare	@bX	binary(10)
select @bX = 235847174
select dbo.BinToString(@bX)
--------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------
--example of usage
----------------------------------------------------------------------------------
Example Two: Image to formatted varchar conversion
create table #img( xImg	image not null)
insert into #img (xImg) values (0x12312312313123123123123123123131321312313)
insert into #img (xImg) values (0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa33333333333333333333333333333333aaaaaaaaaaaaaaaaaaaaaaaaaaaa3)
insert into #img (xImg) values (0xeeeeeeeee4444444444440985345121123948593212aa889ad99e9f99e99)
select 'This is a varchar ' + dbo.BinToString(xImg) from #img
--------------------------------------------------------------------------------*/