Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-26
12
Medium Priority
?
3,545 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
Comment
Question by:pretek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
12 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12417759
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
ID: 12417775
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12418761
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:kselvia
ID: 12418778
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
ID: 12419457
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
ID: 12422804
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
ID: 12422976
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
ID: 12425224
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:
Hilaire earned 2000 total points
ID: 12431098
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

618 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