Métodos Hexadecimal em SQLServer

/*

Representação String to Hex

*/
DECLARE @HEXB AS varbinary(1000) ,
@HEXV AS varchar(1000)

-- Convert hexstring value in a variable to varbinary:

DECLARE @hexstring varchar(max) ;
SET @hexstring = 'abcedf012439' ;
SELECT @HEXB =
CAST('' AS xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )' , 'varbinary(max)')
FROM
( SELECT
CASE substring(@hexstring , 1 , 2)
WHEN '0x' THEN 3
ELSE 0
END ) AS t ( pos )

Select @HEXB,
SQL_VARIANT_PROPERTY(@HEXB,'BaseType') AS '@HEXB Base Type'
GO


/*

Hex p/ representação em string - Function não documentada

*/
select master.dbo.fn_varbintohexstr(@HEXB) as String

--OU

DECLARE @hexbin varbinary(max)
SET @hexbin = 0xabcedf012439
Set @HEXV = '0x' + CAST('' AS xml).value('xs:hexBinary(sql:variable("@hexbin") )' , 'varchar(max)')
Select @HEXV,
SQL_VARIANT_PROPERTY(@HEXV,'BaseType') AS '@HEXV Base Type'
GO


/*

Valor correspondente em string/int

*/

DECLARE @HEXB AS varbinary(1000),
@charvalue as VarChar(1000)
Set @HEXB = 0x46617573746F

declare @vc varchar(8)
declare @vi Int
declare @vb varbinary(8)

set @vb = @HEXB
set @vc = CONVERT(varchar(8),@vb,2)

SELECT @vb, @vc

set @vb = 0x000000C1
set @vc = CONVERT(Int,@vb,2)
SELECT @vb, @vc


/*

Hex p/ representação em string

*/

DECLARE @HEXB AS varbinary(1000),
@charvalue as VarChar(1000)
Set @HEXB = 0x193

Set @charvalue = '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@HEXB") )', 'varchar(max)');

SELECT SQL_VARIANT_PROPERTY(@charvalue,'BaseType') AS '@charvalue Base Type',
SQL_VARIANT_PROPERTY(@charvalue,'Precision') AS '@charvalue Precision',
SQL_VARIANT_PROPERTY(@charvalue,'Scale') AS '@charvalue Scale',
SQL_VARIANT_PROPERTY(@charvalue,'MaxLength') AS '@charvalue MaxLength',
@charvalue as '@charvalue Valor'

SELECT


/*

Conversão de String / Int p/ HEX


*/

declare @hexstring varchar(max);
set @hexstring = '193';

select CONVERT(varbinary(max), @hexstring, 1);

set @hexstring = '193';
select CONVERT(varbinary(max), @hexstring, 2);

declare @hexInt Int;
set @hexInt = '193';
select CONVERT(varbinary(max), @hexInt, 2);