Solved

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

Posted on 2006-11-20
6
4,990 Views
Last Modified: 2012-05-05
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.






0
Comment
Question by:novice12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17980303
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
 

Author Comment

by:novice12
ID: 17980366
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
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17980714
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:novice12
ID: 17980784
Thanks for the help. I am using SQL 7. So I will have to convert it to a stored procedure.
0
 

Author Comment

by:novice12
ID: 17980897
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
 

Author Comment

by:novice12
ID: 17980951
The answer works perfectly. I googled a lot, but this is the best answer by far. Thanks
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question