Hexidecimal to Decimal

Question

Hexidecimal to Decimal

WatchOut4Keith

Old Hand

Points: 399
More actions
April 16, 2005 at 10:21 pm

#66033

There's probably a function already available to do this, but I am asking any way. I know I can convert from decimal to Hex, is there a way to convert back from hex into decimal?
The reason I ask is I have a bunch of hex numbers that are not in binary but are in a string format. The reason is because these are two hex number concatenated together. I have to split the two then convert each separately into decimal. Any ideas?
I need to use this in a function.
Thanks in advance

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

WatchOut4Keith Old Hand Points: 399 More actions · Answer 1

I decided to start from scratch on this and find out how to manually convert the a hex number to a decimal. There was a great article about this on the internet. I then used this logic to figure out how to get the proper result.

(see: http://mathforum.org/library/drmath/view/54311.html for the article)

Create function hexToDec (@hexnum as varchar(100))

returns int

begin

/*

Hex to Decimal function for SQL SERVER

For an explanation of Hex to Decimal conversion see:

http://mathforum.org/library/drmath/view/54311.html

Converts a hex number in a string to a Decimal

Will not work with binary data types

*/

declare @power as int-- Power to raise 16 by

declare @Result as int-- Final result displayed.

declare @Current as int-- Current value currentDigit*(16^N)

DECLARE @LEN AS INT-- Length @Hex,Subtracts 1 each time through the loop

declare @Char as varchar(2) -- String value of current digit in the loop

declare @Num as int -- Interger value of the current digit in the loop

-- Initialize the values

set @power=0

Set @current=0

Set @result=0

set @hexnum=rtrim(ltrim(upper(@hexnum))) -- make hexnum upper case remove leading and trailing spaces

SET @LEN=LEN(@HEXNUM) -- get the length of the string

WHILE @LEN>0

BEGIN

set @char=(SUBSTRING(@HEXNUM,@LEN,1)) -- starts at last digit and moves to first

-- if it's a numeric digit then just pass it in

IF ISNUMERIC(@CHAR)=1

BEGIN

SET @NUM=(CAST(@CHAR AS INT))

END

Else -- if it's not numeric do the following

begin -- get the ASCII number

set @NUM=AscII(@CHAR)

If @NUM70

begin --- if none of the ascii characters are A to F then return null

return(null)

end

else -- get the proper hex value by subtracting 65 from the AscII code and adding 10.

begin

set @NUM=@NUM-65+10

end

end -- end outer else

-- The number value is times by 16^N. N Value starts at zero and is raised by 1 each time through the loop.

set @current=@num*(power(16,@power))

set @result=@result+@current

set @len=@len-1

set @power=@power+1

END

return(@result)

end

David.Poole SSC Guru Points: 76035 More actions · Answer 2

SQL seems to handle it without any problems

Try select CAST(0xF43C2 AS INT)

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Lots of useful ideas you can find here: http://groups.google.de/groups?hl=de&lr=&frame=right&th=71fb677afa372736&seekm=Ouo5SydpEHA.3428%40TK2MSFTNGP11.phx.gbl#link1

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

WatchOut4Keith Old Hand Points: 399 More actions · Answer 4

I tried that one, I couldn't get it to go back into a string. Thanks for the help.

dongadoy SSC Eights! Points: 880 More actions · Answer 5

You would have to use dynamic sql:

declare @hex varchar(10)

set @hex = 'FF'

declare @sql nvarchar(50)

set @sql = N'select @deci = 0x' + @hex

declare @deci int

execute sp_executesql @sql,

N'@deci int output', @deci output

select @deci

Adam Machanic SSCoach Points: 15370 More actions · Answer 6

select CAST(CAST(0xF43C2 AS INT) AS VARCHAR(30))

?

--
Adam Machanic
whoisactive

WatchOut4Keith Old Hand Points: 399 More actions · Answer 7

I need to be able to pass a varchar field into a function to get back the decimal. All my data is stored in a varchar field due to the combined number system being used.

It looks like link in the post above is doing almost the samething as the item I posted earlier is doing only in a lot shorter code. I think I will go with that one. Though I noticed that they are not using the cast statement in their code either. I ran into problems with the cast statement because I was unable to put varchar variables into it and get the correct answer back.

If there is a simplier method of getting cast to convert the varchar variable to a decimal that would be helpful. However with the code offered here I think I have my problem solved. Thank you all.

Adam Machanic SSCoach Points: 15370 More actions · Answer 8

Sorry, I didn't understand the requirements before posting...

As someone else mentioned, dynamic SQL is the way to go in that case.

--
Adam Machanic
whoisactive

noeld SSC Guru Points: 96590 More actions · Answer 9

why do you need to do that? varchar ---> int ---> varchar

how about

declare @s-2 nvarchar(40), @sval int

set @s-2 = 'select @sval = 0x2C'

exec sp_execsql @s-2, N'@sval int out', @sval out

select @sval

* Noel

WatchOut4Keith Old Hand Points: 399 More actions · Answer 10

Well actually it's

declare @s-2 nvarchar(40), @sval int

set @s-2 = 'select @sval = 0x2C'

exec master.dbo.sp_executesql @s-2, N'@sval int out', @sval out

select @sval

And it works great until I try to use it in a function. I got the following error:

Only functions and extended stored procedures can be executed from within a function.

I guess I can't use sp_executesql insided of the function.

Is there an xp_ version?

noeld SSC Guru Points: 96590 More actions · Answer 11

Keith,

As you already discovered it is not posible to use dynamic sql inside a function

Can you offer an example of what you are trying to accomplish?

* Noel

Adam Machanic SSCoach Points: 15370 More actions · Answer 12

There might be a cleaner way to do this -- I just kind of dashed it off -- and I apologize for the bad variable names, but I'm too lazy to change them now ... But here's one way w/ no dynamic SQL:

declare @x varchar(20)
set @x = '0xFFFA01'

set @x = right(@x, len(@x) - 2)
set @x = case len(@x) % 2 when 1 then '0' + @x else @x end

declare @y int
set @y = len(@x)
declare @z char(2)
declare @r int
set @r = 0

declare @out int
set @out = 0

while @y >= 2
begin
set @z = substring(@x, @y - 1, 2)

set @out = @out +
 (case 
when substring(@z, 2, 1) between '0' and '9' then convert(int, substring(@z, 2, 1))
when substring(@z, 2, 1) = 'A' THEN 10
when substring(@z, 2, 1) = 'B' THEN 11
when substring(@z, 2, 1) = 'C' THEN 12
when substring(@z, 2, 1) = 'D' THEN 13
when substring(@z, 2, 1) = 'E' THEN 14
when substring(@z, 2, 1) = 'F' THEN 15
end +
case 
when substring(@z, 1, 1) between '1' and '9' then convert(int, substring(@z, 2, 1)) * 16
when substring(@z, 1, 1) = 'A' THEN 10 * 16
when substring(@z, 1, 1) = 'B' THEN 11 * 16
when substring(@z, 1, 1) = 'C' THEN 12 * 16
when substring(@z, 1, 1) = 'D' THEN 13 * 16
when substring(@z, 1, 1) = 'E' THEN 14 * 16
when substring(@z, 1, 1) = 'F' THEN 15 * 16
else 0
end) * power(2, @r)


set @y = @y - 2
set @r = @r + 8
end


print @out

--
Adam Machanic
whoisactive

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

Just out of curiosity. Did anyone bother to have a look at the link I've posted?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Adam Machanic SSCoach Points: 15370 More actions · Answer 14

Damn, leave it to Steve Kass (mathematician) to come up with a MUCH better solution than mine

--
Adam Machanic
whoisactive