T-SQL::Convert hex value to String 32bit

 Convert Hex value to Signed 32-bit int

 

1. /* ======================================================================== *
    *   Function Name:    fnHexToInt                                           *
    *                                                                          *
    * -- Description --------------------------------------------------------- *
    * Convert, up to 8 nibbles, hex string ('FFFFFFFF'...'00000000')           *
    *      to                                                                  *
    *  Signed 32-bit int (-2147483648..2147483647).                            *
    * -- History ------------------------------------------------------------- *
    * 1.0.0 28.Oct.2001, Ofer Bester                                           *
    * ======================================================================== */
   PRINT '* CREATE FUNCTION: fnHexToInt, Ver. 1.0.0 (28.Oct.2001).'
   SETUSER 'dbo'
   go
2. IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
   BEGIN
       PRINT '  + DROP FUNCTION dbo.fnHexToInt.'
       DROP FUNCTION dbo.fnHexToInt
   END
   PRINT '  + CREATE FUNCTION dbo.fnHexToInt.'
   go
3. CREATE FUNCTION dbo.fnHexToInt (@Hex varchar(8))
   RETURNS int
   -- Ver. 1.0.0 28.Oct.2001, Ofer Bester
   AS BEGIN
       DECLARE @i      tinyint,
               @Nibble tinyint,
               @ch     char(1),
               @Result int
4.     SET @i      = 1                                                     -- Init nibble counter
       SET @Result = 0                                                     -- Init output parameter
5.     SET @Hex     = UPPER( LTRIM( RTRIM( @Hex ) ) )                      -- Convert to uppercase
6.     WHILE (@i <= LEN(@Hex))
       BEGIN
           SET @ch = SUBSTRING(@Hex, @i, 1)
7.         IF      (@ch >= '0' AND @ch <= '9') SET @Nibble = ASCII(@ch) - ASCII('0')
           ELSE IF (@ch >= 'A' AND @ch <= 'F') SET @Nibble = ASCII(@ch) - ASCII('A') +10
           ELSE RETURN NULL
8.         IF( @Result > 0x7FFFFFF)                                        -- 134217727 = 0x7FFFFFF
           BEGIN
               SET @Result = @Result & 0x7FFFFFF                           -- Set MSB, of 7 nibbles, OFF
               SET @Result = @Result * 16 + @Nibble +0x80000000            -- Shift left 4Bits, Add last nibble and convert to negetive number.
           END
           ELSE BEGIN
              SET @Result = @Result *16 +@Nibble                           -- Shift left 4Bits, Add nibble.
           END
9.         SET @i = @i +1                                                  -- Next nibble.
       END -- While
10.     RETURN ( @Result )
    END -- Function
    go
11. -- Check create result -------------------------------------------------------
    IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
    BEGIN
        PRINT 'DONE (CREATE FUNCTION: fnHexToInt).'
    END
    ELSE BEGIN
        PRINT '****** FAILES (CREATE FUNCTION: fnHexToInt) ******'
    END
    PRINT ''
    go
12. SETUSER
    go

이 글은 스프링노트에서 작성되었습니다.