|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
July 3rd, 2008, 04:20 PM
|
|||
|
|||
|
Converting hex varchar to decimal bigint
Hi there,
I'm trying to convert the varchar MAC address '0000123ff2a5' (hex) into it's decimal representation as a bigint. I've tried several T-SQL functions but none of them have successfully converted the value. The result should be '0000306180773158'. So, what I'm trying to do is convert a MAC address (hex) into the equipment address (decimal). Any ideas? Thanks so much, Kristina
|
|
#2
July 9th, 2008, 09:39 AM
|
|||
|
|||
|
Code:
create function hex2int(@s varchar(16)) --Convert hex to
returns bigint -- e.g. select dbo.hex2int('7ff2a5')
as begin
select @s=upper(@s)
declare @i int, @len int, @c char(1), @result bigint
select @len=len(@s), @i=@len, @result=case when @len>0 then 0 end
while (@i>0)
begin
select @c=substring(@s,@i,1), @result=@result
+(ASCII(@c)
-(case when @c between 'A' and 'F' then 55
else case when @c between '0' and '9' then 48 end
end))
*power(16.,@len-@i)
,@i=@i-1
end -- while
return @result
end -- function
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Converting hex varchar to decimal bigint |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
