Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate string and number conversion to hexadecimal.
-- T-SQL converting string / character to hexadecimal
SELECT CONVERT(varbinary(1),'A')
-- 0x41
SELECT CONVERT(varbinary(max),'ABCDEFGHIJK')
-- 0x4142434445464748494A4B
SELECT CONVERT(varbinary(4),N'AB') -- UNICODE trailing byte 0
-- 0x41004200
SELECT CONVERT(varbinary(2),N'ő') -- UNICODE trailing byte non-zero
-- 0x5101 ------------
USE AdventureWorks;
GO
-- Convert number to hexadecimal string - Convert number to hex
-- User-defined function - UDF - scalar-valued function
CREATE FUNCTION fnHexadecimal
(@Input VARBINARY(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @HexDigits CHAR(16),
@Result VARCHAR(255),
@i INT,
@InputLength INT
DECLARE @RunningI INT,
@LeftHalf INT,
@RightHalf INT
SET @Result = '0x'
SET @i = 1
SET @InputLength = DATALENGTH(@Input)
SET @HexDigits = '0123456789ABCDEF'
WHILE (@i <= @InputLength)
BEGIN
SET @RunningI = CONVERT(INT,SUBSTRING(@Input,@i,1))
SET @LeftHalf = FLOOR(@RunningI / 16)
SET @RightHalf = @RunningI - (@LeftHalf * 16)
SET @Result = @Result + SUBSTRING(@HexDigits,@LeftHalf + 1,1) + SUBSTRING(@HexDigits,@RightHalf + 1,1)
SET @i = @i + 1
END
RETURN @Result
END
GO
SELECT HEX = dbo.fnHexadecimal(255)
-- 0x000000FF
SELECT HEX = dbo.fnHexadecimal(4096)
-- 0x00001000
SELECT HEX = dbo.fnHexadecimal(999999999999999999999999999999999999)
-- 0x24000001FFFFFFFF0F9F4BB31507C97BCE97C000
-- Convert number to varbinary hex
SELECT CAST( 4096 as varbinary)
-- 0x00001000
-- Convert number to varbinary hex
SELECT CAST( 4095*4095 as varbinary)
-- 0x00FFE001 |