Converting varbinary to char or varchar

How to convert a varbinary column into a char or varchar column.

Rushendra

If you just want to have the varbinary data represented as varchar or char without further processing (e.g. getting the actual meaning of the varbinary hexadecimal stuff) you have 2 system functions you can use.

(1)fn_varbintohexstr
(2)fn_varbintohexsubstring

Both are in the master database and they return 'nvarchar' values.


NHO

Thanks for ur info. I could'nt see this functions in SQL 7. Is there any alternative.

quote:Originally posted by vbkenya

If you just want to have the varbinary data represented as varchar or char without further processing (e.g. getting the actual meaning of the varbinary hexadecimal stuff) you have 2 system functions you can use.

(1)fn_varbintohexstr
(2)fn_varbintohexsubstring

Both are in the master database and they return 'nvarchar' values.


NHO

Rushendra

You can write your own stored procedure using the code in fn_varbintohexsubstring(). There's doesn't seem to be anything particular to sql 2000 so it should work. If you need the source code let me know.

Bambola.

If u give the code that will be helpfull for me

Thanks

quote:Originally posted by bambola

You can write your own stored procedure using the code in fn_varbintohexsubstring(). There's doesn't seem to be anything particular to sql 2000 so it should work. If you need the source code let me know.

Bambola.

Rushendra

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


--
-- System UDF fn_varbintohexsubstring
-- This function takes as input a varbinary, and start offset and length
--and returns the Hexadecimal representation string for the given substring
--
create function dbo.fn_varbintohexsubstring (
@fsetprefix bit = 1-- append '0x' to the output
,@pbinin varbinary(8000) -- input binary stream
,@startoffset int = 1 -- starting offset
,@cbytesin int = 0 -- length of input to consider, 0 means total length
)
returns nvarchar(4000)
as
begin
declare @pstrout nvarchar(4000)
,@i int
,@firstnibble int
,@secondnibble int
,@tempint int
,@hexstring char(16)

--
-- initialize and validate
--
if (@pbinin IS NOT NULL)
begin
select @i = 0
,@cbytesin = case when (@cbytesin > 0) then @cbytesin else DATALENGTH(@pbinin) end
,@pstrout = case when (@fsetprefix = 1) then N'0x' else N'' end
,@hexstring = '0123456789abcdef'

if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 < 1) )
return NULL

if ( ( @startoffset > @cbytesin ) or ( @startoffset < 1 ) )
return NULL

--
-- do for each byte
--
while (@i < @cbytesin)
begin
--
-- Each byte has two nibbles
-- which we convert to character
--
select @tempint = cast(substring(@pbinin, @i + @startoffset, 1) as int)
select @firstnibble = @tempint / 16
select @secondnibble = @tempint % 16

--
-- we need to do an explicit cast with substring
-- for proper string conversion.
--
select @pstrout = @pstrout +
cast(substring(@hexstring, (@firstnibble+1), 1) as nvarchar) +
cast(substring(@hexstring, (@secondnibble+1), 1) as nvarchar)

select @i = @i + 1
end
end

-- All done
return @pstrout
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


--
-- System UDF fn_varbintohexstr
-- This function takes as input a varbinary
--and returns the Hexadecimal representation string
--
create function dbo.fn_varbintohexstr (
@pbinin varbinary(8000) )
returns nvarchar(4000)
as
begin
declare @pstrout nvarchar(4000)

select @pstrout = master.dbo.fn_varbintohexsubstring(1,@pbinin,1,0)
return @pstrout
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Bambola.

Hi,

How can I get the meaning of a varbinary hexadecimal?

Ionel

Binary data consists of hexadecimal numbers. For example, the decimal number 245 is hexadecimal F5. Binary data is stored using the binary, varbinary SQL Server. A column that is assigned the binary data type must have the same fixed length (up to 8 KB) for each row. In a column that is assigned the varbinary data type, entries can vary in the number of hexadecimal digits (up to 8 KB) they contain.

For more info Pl see topic data types in sql server books online.

Thanks.

quote:Originally posted by icata

Hi,

How can I get the meaning of a varbinary hexadecimal?

Ionel

Rushendra

Just converting the hexadecimal value to a 'legible' type(varchar,integer etc) may not unravel the actual 'meaning' of data. The meaning will depend on exactly why it is saved in the database in the first place.

Before trying to work out the meaning of most hexadecimal representations of data, remember that SQL Server will more often than not save the data in reverse hexadecimal order. You will encounter this in the transaction log records if you have tried analyzing them.

To keep it simple:
-Convert every 2 hexadecimal digits in your data to an integer value (two hexadecimal characters represent 1 byte of varchar data)
-Convert the resulting integer values to their ASCII character equivalents
-Try to make sense of the resulting concatenation of ASCII character values.

This may not work for all Hex data, of course.

Nathan H.O.
Moderator
SQL-Server-Performance.com