Solved

Help needed for Transact SQL version of Oracle Hex to Decimal Function

Posted on 2004-10-26
3,459 Views
Last Modified: 2008-01-09
I found the Oracle function given below. I'm looking for the Transact SQL version of this function for MS SQLServer 2000.

I would like to use the function to take a hexidecimal value like
087FFF80000001FA
and using a string function, break it into component parts to convert the hex to decimal, such as:
087 --> 135
FFF8  --> 65528
0000001FA  -->  506
The function has to handle hex values as large as 64 characters.

Select fn_hextodec(left(hexval, 3)) as 'asdf', fn_hextodec(substring(hexval, 4,4)) as 'ghjk', ... from table

Thanks

Oracle function:
FUNCTION hex_to_dec (hexin IN VARCHAR2) RETURN NUMBER IS
  v_charpos NUMBER;
  v_charval CHAR(1);
  v_return NUMBER DEFAULT 0;
  v_power NUMBER DEFAULT 0;
  v_string VARCHAR2(2000);
BEGIN
  v_string := UPPER(hexin);
  v_charpos := LENGTH(v_string);
  WHILE v_charpos > 0 LOOP
    v_charval := SUBSTR(v_string,v_charpos,1);
    IF v_charval BETWEEN '0' AND '9' THEN
      v_return := v_return + TO_NUMBER(v_charval) * POWER(16,v_power);
    ELSE
      IF v_charval = 'A' THEN
        v_return := v_return + 10 * POWER(16,v_power);
      ELSIF v_charval = 'B' THEN
        v_return := v_return + 11 * POWER(16,v_power);
      ELSIF v_charval = 'C' THEN
        v_return := v_return + 12 * POWER(16,v_power);
      ELSIF v_charval = 'D' THEN
        v_return := v_return + 13 * POWER(16,v_power);
      ELSIF v_charval = 'E' THEN
        v_return := v_return + 14 * POWER(16,v_power);
      ELSIF v_charval = 'F' THEN
        v_return := v_return + 15 * POWER(16,v_power);
      ELSE
        raise_application_error(-20621,'Invalid input');
      END IF;
    END IF;
    v_charpos := v_charpos - 1;
    v_power := v_power + 1;
  END LOOP;
  RETURN v_return;
END hex_to_dec;
0
Question by:pretek
    9 Comments
     
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi pretek

    HEX2DEC Function on this page:
    http://www.sitepoint.com/forums/archive/index.php/t-42104.html

    May get you a little closer

    HEX2DEC  is also an Access application built in function
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/SEBB8.asp

    take care...

    Alan
    0
     
    LVL 26

    Expert Comment

    by:Alan Warren
    0
     
    LVL 12

    Expert Comment

    by:kselvia
    I don't know how it will perform. It's set based so maybe it performs better than the loop from the oracle version. No telling.  It's not tested I just threw it together so buyer beware.

    CREATE FUNCTION fn_hextodec(@hex varchar(255))
    RETURNS decimal(20,0)
    AS
    BEGIN
    DECLARE @dec decimal(28,0)
    SET @dec = 0
    SELECT @dec = @dec + power(16, (v.number)) *
          (charindex(SubString(reverse(right(@hex,patindex('%[123456789ABCDEF]%',@hex))),
          (v.number+1), 1),'0123456789ABCDEF') - 1)
    FROM master..spt_values v WHERE (v.number+1) <= Len(Right(@hex,patindex('%[123456789ABCDEF]%',@hex))) and v.type = 'P'
    RETURN @dec
    END
    0
     
    LVL 12

    Expert Comment

    by:kselvia
    I tried it for something other than '087' and it failed, so scratch that because I don't have time to debug it right now :(
    0
     
    LVL 26

    Expert Comment

    by:Hilaire
    This version will handle values up to 'ffffffffffffffffffffffffffffff'   (any hex string up to 31 characters)

    drop function fn_hextodec
    go
    CREATE FUNCTION fn_hextodec(@hex varchar(31))
    RETURNS decimal(38,0)
    AS
    BEGIN
    DECLARE @dec decimal(38,0)
    SET @dec = 0
    SELECT @dec = @dec + power(cast(16 as decimal(28,0)), (len(@hex) - v.number)) * charindex(substring(@hex, v.number, 1),'123456789ABCDEF')
    FROM master..spt_values v WHERE v.number <= Len(@hex) and v.type = 'P' and charindex('[^0123456789ABCDEF]', @hex) = 0
    RETURN @dec
    END
    go
    -- to use it don't omit the owner prefix (dbo.)
    select dbo.fn_hextodec('ffffffffffffffffffffffffffffff')

    -- with your data

    declare @str varchar(200)
    set @str = '087FFF80000001FA'
    select dbo.fn_hextodec(left(@str,3)), dbo.fn_hextodec(substring(@str, 4, 4)),
    dbo.fn_hextodec(right(@str,9))

    HTH

    Hilaire




    0
     

    Author Comment

    by:pretek
    Hiliare, merci.

    Best response so far and one I will accept.

    I tried to convert  the oracle version and came up with the following but I can't figure what is causing the syntax errors:

    CREATE FUNCTION [dbo].[hextodec](@hexin varchar(50))  
    RETURNS BIGINT
    AS  
    BEGIN
      DECLARE @charpos INT, @char_value CHAR(1), @return_value BIGINT, @power_value INT, @string_value VARCHAR(2000)
      SET @return_value = 0
      SET @power_value = 0
      SET @string_value = UPPER(@hexin)
      SET @charpos = LEN(@string_value)
      LOOPBLOCK:
         SET @char_value = SUBSTRING(@string_value,@charpos,1)
         IF @char_value BETWEEN '0' AND '9'
           SET @return_value = @return_value + CAST(@char_value AS int) * POWER(16,@power_value)
         CASE @char_value
             When 'A' THEN
                SET @return_value = @return_value + 10 * POWER(16,@power_value)
             When 'B' THEN
                SET @return_value = @return_value + 11 * POWER(16,@power_value)
             When 'C' THEN
               SET @return_value = @return_value + 12 * POWER(16,@power_value)
             When 'D' THEN
               SET @return_value = @return_value + 13 * POWER(16,@power_value)
             When 'E' THEN
               SET @return_value = @return_value + 14 * POWER(16,@power_value)
             When 'F' THEN
               SET @return_value = @return_value + 15 * POWER(16,@power_value)
           ELSE
               RAISERROR ('Input value characters must be between 0-9 or A-F.', 16, 1)
           END
         SET @charpos = @charpos - 1
         SET @power_value = @power_value + 1
        IF @charpos > 0 GOTO LOOPBLOCK
      RETURN @return_value
    END
    END

    I'm loading a database full of hex values to process and I'd like to compare the long way vs. Hiliare's way and I'll report the difference.
    0
     
    LVL 26

    Expert Comment

    by:Hilaire
    Hi pretek,
    Here's your function in T-SQL valid syntax.

    RAISERROR can't be used inside a UDF (i return 0 instead, but this could be NULL as well)

    No labels in T-SQL  ( and no goto )  : used while loop instead

    Corrected case ... when syntax

    CREATE FUNCTION [dbo].[hextodec](@hexin varchar(50))  
    RETURNS BIGINT
    AS  
    BEGIN
      DECLARE @charpos INT, @char_value CHAR(1), @return_value BIGINT, @power_value INT, @string_value VARCHAR(2000)
      SET @return_value = 0
      SET @power_value = 0
      SET @string_value = UPPER(@hexin)
      SET @charpos = LEN(@string_value)
      WHILE @charpos > 0
      BEGIN
         SET @char_value = SUBSTRING(@string_value,@charpos,1)
         IF CHARINDEX(@char_value, '0123456879ABCDEF') = 0
             RETURN 0
         SET @return_value = @return_value + CASE @char_value
             When 'A' THEN 10 * POWER(16,@power_value)
             When 'B' THEN 11 * POWER(16,@power_value)
             When 'C' THEN 12 * POWER(16,@power_value)
             When 'D' THEN 13 * POWER(16,@power_value)
             When 'E' THEN 14 * POWER(16,@power_value)
             When 'F' THEN 15 * POWER(16,@power_value)
             else CAST(@char_value AS int) * POWER(16,@power_value) -- this is for '0' to '9'
           END
         SET @charpos = @charpos - 1
         SET @power_value = @power_value + 1
        END
      RETURN (@return_value)
    END

    Hilaire
    0
     

    Author Comment

    by:pretek
    Hilaire,

    I'm not sure what I'm doing wrong. When I check the syntax I get the following on SQL Server 2000 Standard.

    Microsoft SQL-DMO (ODBC SQLState: 42000)
    Error 170: Line 1 Incorrect syntax near ''.
    Line 6: Incorrect syntax near ''.
    Line 7: Incorrect syntax near ''.
    Line 8: Incorrect syntax near ''.
    Line 9: Incorrect syntax near ''.
    Must declare the variable '@string_value'.
    Line 11: Incorrect syntax near ''.
    Must declare the variable '@string_value'.
    Line 14: Incorrect syntax near ''.
    Line 16: Incorrect syntax near ''.
    Line 25: Incorrect syntax near ''.
    Line 26: Incorrect syntax near ''.
    A RETURN statement with a return value cannot be used in this context.

    This is why I used goto instead of While so I did not have variable scope issue from the nested Begin End for While.
    0
     
    LVL 26

    Accepted Solution

    by:
    The version above definitely compiles on SQL 2000.

    To create a function/stored procedure, use query analyser rather than Enterprise manager.
    Issue a drop-create statement as follows

    drop function [dbo].[hextodec]
    go
    CREATE FUNCTION [dbo].[hextodec](@hexin varchar(50))  
    RETURNS BIGINT
    AS  
    BEGIN
      DECLARE @charpos INT, @char_value CHAR(1), @return_value BIGINT, @power_value INT, @string_value VARCHAR(2000)
      SET @return_value = 0
      SET @power_value = 0
      SET @string_value = UPPER(@hexin)
      SET @charpos = LEN(@string_value)
      WHILE @charpos > 0
      BEGIN
         SET @char_value = SUBSTRING(@string_value,@charpos,1)
         IF CHARINDEX(@char_value, '0123456879ABCDEF') = 0
             RETURN 0
         SET @return_value = @return_value + CASE @char_value
             When 'A' THEN 10 * POWER(16,@power_value)
             When 'B' THEN 11 * POWER(16,@power_value)
             When 'C' THEN 12 * POWER(16,@power_value)
             When 'D' THEN 13 * POWER(16,@power_value)
             When 'E' THEN 14 * POWER(16,@power_value)
             When 'F' THEN 15 * POWER(16,@power_value)
             else CAST(@char_value AS int) * POWER(16,@power_value) -- this is for '0' to '9'
           END
         SET @charpos = @charpos - 1
         SET @power_value = @power_value + 1
        END
      RETURN (@return_value)
    END
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction After workin in a plethora of programming languages like C, Pro*C, ESQL/C, C++, VC++, VB, Java, HTML,JavaScipt etc, technologies and frameworks like JSP, Servlets, Struts, Spring, IBatis etc and databases like MS Access, SQLServer, Inf…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now