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
*/

This page was last updated on May 01, 2006 04:28 PM.