Convert or cast HexaDecimal to Bigint

Hi ,

I have a hexadecimal string value. I want to convert it to Bigint in Sql Server 2005.

The Hexadecimal value is '0x000000000000000F'.

How is it possible to convert into Bigint.

Please help me

Thanks in advance

Srinivas

SELECT CONVERT(bigint, 0x000000000000000F)|||

There are many ways to convert a string with hexadecimal value to integer value. Below is one technique using a table of numbers:

declare @.t varchar (30)
select @.t = '000000000000000F'
select sum(
case lower( substring( reverse(@.t), number , 1 ) )
when '0' then 0
when '1' then 1
when '2' then 2
when '3' then 3
when '4' then 4
when '5' then 5
when '6' then 6
when '7' then 7
when '8' then 8
when '9' then 9
when 'a' then 10
when 'b' then 11
when 'c' then 12
when 'd' then 13
when 'e' then 14
when 'f' then 15
when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
end * power( cast(16 as bigint), number - 1 )
)
from Numbers n
where number between 1 and len( @.t )
go

Note that it doesn't handle conversions into negative bigint values but that can be done easily.

|||

Hi ,

I think directly using Cast or Convert will not work.

The Bigint value corresponding to Hexadecimal value ('0x000000000000000F') is 1000000002.

I require a function which would take Hexadecimal value as input parameters and return me a big int value.

Thanks for the help.

Srinivas Govada

|||Then create a user-defined function with the code given by Umachandar and pass the hex string to that function and get the calculated bigint value as the return value from the function.|||

Hi

What does number refer to and what is there in table Numbers.

Please provide structure of table numbers and records in table.

Regards,

Srinivas Govada