novice12
asked on
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:
0x1A000000FCFF12000200371D 8041591B40 3C381D8041 591B403C
I needed to get rid of the leading 23 characters, and I did it like this:
SELECT SUBSTRING(master.dbo.fn_va rbintohexs tr(hexcode ),23,32)
That gives me the following:371D8041591B403C 381D804159 1B403C
I use more string operations as follows:
select HexCode,
Convert(int,(Lo1+Lo2+Lo3+L o4)) as NewIntCode
from
(
SELECT ObjectIdHi, objectIdLo, uiName, HexCode,
Substring(substring(master .dbo.fn_va rbintohexs tr(HexCode ),23,8), 7, 2) Lo1,
Substring(substring(master .dbo.fn_va rbintohexs tr(HexCode ),23,8), 5, 2) Lo2,
Substring(substring(master .dbo.fn_va rbintohexs tr(HexCode ),23,8), 3, 2) Lo3,
Substring(substring(master .dbo.fn_va rbintohexs tr(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.
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:
0x1A000000FCFF12000200371D
I needed to get rid of the leading 23 characters, and I did it like this:
SELECT SUBSTRING(master.dbo.fn_va
That gives me the following:371D8041591B403C
I use more string operations as follows:
select HexCode,
Convert(int,(Lo1+Lo2+Lo3+L
from
(
SELECT ObjectIdHi, objectIdLo, uiName, HexCode,
Substring(substring(master
Substring(substring(master
Substring(substring(master
Substring(substring(master
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. I am using SQL 7. So I will have to convert it to a stored procedure.
ASKER
Please disregard my last comment, it's actually SQL 2000. Sorry for the confusion, I will try the above function as you suggested. Thanks
ASKER
The answer works perfectly. I googled a lot, but this is the best answer by far. Thanks
select HexCode,
Convert(int,cast((Lo1+Lo2+
from
(
SELECT ObjectIdHi, objectIdLo, uiName, HexCode,
Substring(substring(master
Substring(substring(master
Substring(substring(master
Substring(substring(master
FROM MyTable
)
as NewTable