DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to convert numbers to hexadecimal?

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE