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