August 5, 2015 at 9:44 am
I know this function is not supported by Microsoft, but some of the developers insisted on using it anyway on two of our 2k8 r2 databases.
Is there an 'easy' way to replace it with something to does the same thing?
August 5, 2015 at 10:42 pm
A quick check of the function's source :
USE master;
GO
EXEC sp_helptext 'fn_varbintohexstr';
GO
Reveals the source code:
create function sys.fn_varbintohexstr
(
@pbinin varbinary(max)
)
returns nvarchar(max)
as
begin
return sys.fn_varbintohexsubstring(1,@pbinin,1,0)
end
OK, that just calls sys.fn_varbintohexsubstring. Let's look at that:
USE master;
GO
EXEC sp_helptext 'fn_varbintohexsubstring';
GO
There's the source code for the proc. You can grab it and create your own, or write something else:
create function sys.fn_varbintohexsubstring (
@fsetprefix bit = 1-- append '0x' to the output
,@pbinin varbinary(max) -- input binary stream
,@startoffset int = 1 -- starting offset
,@cbytesin int = 0 -- length of input to consider, 0 means total length
)
returns nvarchar(max)
as
begin
declare @pstrout nvarchar(max)
,@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 and @cbytesin <= DATALENGTH(@pbinin) ) then @cbytesin else DATALENGTH(@pbinin) end
,@pstrout = case when (@fsetprefix = 1) then N'0x' else N'' end
,@hexstring = '0123456789abcdef'
--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters
if ( ((@cbytesin * 2) + 2 > 1073741824) or ((@cbytesin * 2) + 2 < 1) or ( @cbytesin is null ))
return NULL
if ( ( @startoffset > DATALENGTH(@pbinin) ) or ( @startoffset < 1 ) or ( @startoffset is null ))
return NULL
--
-- adjust the length to process based on start offset and
-- total length
--
if ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)
select @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1
--
-- 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
-Eddie
Eddie Wuerch
MCM: SQL
August 6, 2015 at 12:24 am
Manic Star (8/5/2015)
I know this function is not supported by Microsoft, but some of the developers insisted on using it anyway on two of our 2k8 r2 databases.Is there an 'easy' way to replace it with something to does the same thing?
Don't use the fn_varbintohexstr function, it's an old relic. Use the CONVERT function instead.
Suggest that you set the BOL CAST/CONVERT page as your developers default webpage;-)
😎
DECLARE @BINSTR AS VARBINARY(16) = 0xD8B3C7D60227F047A89A3738FEF51BE9;
SELECT
CONVERT(VARCHAR(34),@BINSTR,1) AS BINSTR_WITH_0x
,CONVERT(VARCHAR(34),@BINSTR,2) AS BINSTR_WITHOUT_0x;
Output
BINSTR_WITH_0x BINSTR_WITHOUT_0x
---------------------------------- ----------------------------------
0xD8B3C7D60227F047A89A3738FEF51BE9 D8B3C7D60227F047A89A3738FEF51BE9
August 6, 2015 at 12:30 am
Eddie Wuerch (8/5/2015)
A quick check of the function's source :
USE master;GO
EXEC sp_helptext 'fn_varbintohexstr';
GO
Reveals the source code:
create function sys.fn_varbintohexstr(
@pbinin varbinary(max)
)
returns nvarchar(max)
as
begin
return sys.fn_varbintohexsubstring(1,@pbinin,1,0)
end
OK, that just calls sys.fn_varbintohexsubstring. Let's look at that:
USE master;GO
EXEC sp_helptext 'fn_varbintohexsubstring';
GO
There's the source code for the proc. You can grab it and create your own, or write something else:
create function sys.fn_varbintohexsubstring (
@fsetprefix bit = 1-- append '0x' to the output
,@pbinin varbinary(max) -- input binary stream
,@startoffset int = 1 -- starting offset
,@cbytesin int = 0 -- length of input to consider, 0 means total length
)
returns nvarchar(max)
as
begin
declare @pstrout nvarchar(max)
,@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 and @cbytesin <= DATALENGTH(@pbinin) ) then @cbytesin else DATALENGTH(@pbinin) end
,@pstrout = case when (@fsetprefix = 1) then N'0x' else N'' end
,@hexstring = '0123456789abcdef'
--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters
if ( ((@cbytesin * 2) + 2 > 1073741824) or ((@cbytesin * 2) + 2 < 1) or ( @cbytesin is null ))
return NULL
if ( ( @startoffset > DATALENGTH(@pbinin) ) or ( @startoffset < 1 ) or ( @startoffset is null ))
return NULL
--
-- adjust the length to process based on start offset and
-- total length
--
if ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)
select @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1
--
-- 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
-Eddie
Few years back (pre 2008) I did an optimized version of this function, roughly halved the execution time (for a specific length (16/32)). Since 2008 there is no point using it as the CONVERT is faster, already there and fully documented.
😎
August 6, 2015 at 11:20 am
Thanks everyone! They ended up using CONVERT instead.
August 6, 2015 at 11:55 am
Manic Star (8/6/2015)
Thanks everyone! They ended up using CONVERT instead.
Good stuff and you are very welcome
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply