MS T-SQL how to convert an nvarchar to hex and then to integer?

I am using MS SQL-7
I am getting the error: Syntax error converting the nvarchar value '41801d37' to a column of data type int.
My question in summary is: How to convert a data of type "nvarchar" that represent a hex value to an integer vlaue?
In other words how to convert 41801d37 to 1098915127

*****************************************************
Now I am going to provide some details:
I have a table with a column called "hexcode"
The data type in this column is varbinary with a length of 12, and looks like this:
0x1A000000FCFF12000200371D8041591B403C381D8041591B403C
I needed to get rid of the leading 23 characters, and I did it like this:
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(hexcode),23,32)
That gives me the following:371D8041591B403C381D8041591B403C

I use more string operations as follows:

select  HexCode,
          Convert(int,(Lo1+Lo2+Lo3+Lo4)) as NewIntCode
from
(
SELECT ObjectIdHi, objectIdLo, uiName, HexCode,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 7, 2) Lo1,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 5, 2) Lo2,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 3, 2) Lo3,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 1, 2) Lo4
FROM MyTable
)
as NewTable

My problem is that when I run the query I get the error:
Syntax error converting the nvarchar value '41801d37' to a column of data type int.






novice12Asked:
Who is Participating?
 
NightmanConnect With a Mentor CTOCommented:
OK - a bit of a challenge. Instead of writing all of the base converion myself (brain is already tired) I googled a bit - from http://www.tek-tips.com/viewthread.cfm?qid=1192627&page=1

Creat a function in your database as below:

create function base2int( @num varchar(16), @radix tinyint )
returns bigint
as
begin
    declare @ret bigint; set @ret = 0
    declare @chars char(16); set @chars = '0123456789ABCDEF'
    declare @base bigint; set @base = 1
    declare @i tinyint; set @i = len(@num)
    declare @pos tinyint

    while @i >= 1 and @ret is not null
    begin
        set @pos = charindex(substring(@num, @i, 1), @chars)
        if @pos = 0
            set @ret = null
        else
            set @ret = @ret + @base * (@pos-1)

        set @i = @i-1
        set @base = @base * @radix
    end

    return @ret
end
go

Then
select  HexCode,
          dbo.base2int((Lo1+Lo2+Lo3+Lo4),16) as NewIntCode
from
(
SELECT ObjectIdHi, objectIdLo, uiName, HexCode,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 7, 2) Lo1,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 5, 2) Lo2,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 3, 2) Lo3,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 1, 2) Lo4
FROM MyTable
)
as NewTable


That should work fine.
0
 
NightmanCTOCommented:
Give this a try:

select  HexCode,
          Convert(int,cast((Lo1+Lo2+Lo3+Lo4) as varbinary)) as NewIntCode
from
(
SELECT ObjectIdHi, objectIdLo, uiName, HexCode,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 7, 2) Lo1,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 5, 2) Lo2,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 3, 2) Lo3,
Substring(substring(master.dbo.fn_varbintohexstr(HexCode),23,8), 1, 2) Lo4
FROM MyTable
)
as NewTable
0
 
novice12Author Commented:
When I run the above query, I don't get any errors. But the value of the output is wrong. If for example the hex value is 41801d37, the output I am getting is 855652096, when the right integer value is 1098915127.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
novice12Author Commented:
Thanks for the help. I am using SQL 7. So I will have to convert it to a stored procedure.
0
 
novice12Author Commented:
Please disregard my last comment, it's actually SQL 2000. Sorry for the confusion, I will try the above function as you suggested. Thanks
0
 
novice12Author Commented:
The answer works perfectly. I googled a lot, but this is the best answer by far. Thanks
0
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.

All Courses

From novice to tech pro — start learning today.