Binaryworld - How to convert integer values to hexadecimal strings in SELECT stateme ... [ SQL Server -> T-SQL ]

DECLARE @IntVal int
SELECT @IntVal = 24
SELECT '0x' +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 7 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 6 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 5 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 4 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 3 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 2 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 1 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( HexStr , ( @IntVal / POWER( 16 , 0 ) ) % 16 + 1 , 1 ) AS HexVal
FROM (
SELECT '0123456789ABCDEF' AS HexStr
) AS a
-- Output of SELECT statement:
/*
HexVal
----------
0x00000018
*/

-- SQL70 / 2000 only
-- Using a computed column in a table
CREATE TABLE #t (
IntVal int ,
HexStr AS ( '0x' +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 7 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 6 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 5 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 4 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 3 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 2 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 1 ) ) % 16 + 1 , 1 ) +
    SUBSTRING( '0123456789ABCDEF' , ( IntVal / POWER( 16 , 0 ) ) % 16 + 1 , 1 )
)
);

INSERT INTO #t VALUES( 13 );
INSERT INTO #t VALUES( 11345 );
INSERT INTO #t VALUES( 721024 );
INSERT INTO #t DEFAULT VALUES;

SELECT * FROM #t;
-- Output
/*
IntVal   HexStr
----------- ----------
   11345 0x00002C51
   721024 0x000B0080
NULL    NULL
     13 0x0000000D
*/