# 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;
###### 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.

Applications DeveloperCommented:
Hi pretek

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
Applications DeveloperCommented:
0
RetiredCommented:
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
RetiredCommented:
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
Commented:
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')

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 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
Commented:
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 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
Commented:
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

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

###### 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.