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

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;
pretekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
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
Alan WarrenApplications DeveloperCommented:
0
Ken SelviaRetiredCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ken SelviaRetiredCommented:
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
HilaireCommented:
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
pretekAuthor Commented:
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
HilaireCommented:
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
pretekAuthor Commented:
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
HilaireCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.