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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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