Converting hex to int
-
The following qry returns hex values. I need to know if there is a way to convert the hex values to int.
Select substring(msg_xml, charindex('CDATA[', msg_xml, 0) +18 ,2)
from
where substring(msg_xml, charindex('CDATA[', msg_xml, 0) +18 ,2) = '0A'
Is there a way?
Thanks!
-
Hi timingskey,
quote:
The following qry returns hex values. I need to know if there is a way to convert the hex values to int.Select substring(msg_xml, charindex('CDATA[', msg_xml, 0) +18 ,2)
from
where substring(msg_xml, charindex('CDATA[', msg_xml, 0) +18 ,2) = '0A'
Is there a way?
yes, there is.
DECLARE @hex varchar(10)
DECLARE @stmt nvarchar(255)
DECLARE @int int
SET @hex = '0x8A'
SELECT @stmt = N'Select @int = convert( int , ' + @hex + ' )'
EXEC sp_ExecuteSql @stmt, N'@int Int Out', @int OUT
SELECT @int
GO
Should return 138
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank. This helps. Th eonly other thing is that I'm trying to do this without putting anything in a variable.
The original query returns many rows. Is there an easy way to just convert the entire reult set?
-
Hi timingskey,
quote:
Thanks Frank. This helps. Th eonly other thing is that I'm trying to do this without putting anything in a variable.The original query returns many rows. Is there an easy way to just convert the entire reult set?
what about
SELECT CONVERT(int, your_field) as blabla FROM your_table ?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I wish it was that easy. I'm getting an error when I that says I can't convert varchar to int.
The app developer that I am working with on this wrote the query. He also has a user-defined function that he created to convert the data to int.
I would rather not use his user-defined function if there is a way to do it.
I'm thinking of allowing him to create and drop his user-defined function each time he needs to use it.
Thanks again.
-
quote:
I wish it was that easy. I'm getting an error when I that says I can't convert varchar to int.The app developer that I am working with on this wrote the query. He also has a user-defined function that he created to convert the data to int.
I would rather not use his user-defined function if there is a way to do it.
I'm thinking of allowing him to create and drop his user-defined function each time he needs to use it.
so the underlying data is a varchar and not a binary or varbinary?
Take a look at 'CAST and CONVERT' in BOL.
Maybe you have to first convert varchar into varbinary and then back to int.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you so much! Converting to varbinary and then to int worked.
-
I'd be interested to see this one. When I use SELECT CONVERT( INT, CONVERT( VARBINARY, '0x8A' ) ) the answer comes 813185089 instead of the 138 I had hoped for. What did you use to get the correct VARBINARY value?
Guarddata-
-
Hi guarddata,
quote:
I'd be interested to see this one. When I use SELECT CONVERT( INT, CONVERT( VARBINARY, '0x8A' ) ) the answer comes 813185089 instead of the 138 I had hoped for. What did you use to get the correct VARBINARY value?to a certain point you're right. But I think the problem is something else
DECLARE @hex varchar(10)
SET @hex = '0x8A'
SELECT CONVERT(varbinary,@hex)
you should receive 0x30783841
Now recalculating 30783841 to decimal obvious is 813185089 and that's what you (and I) get.
SELECT CONVERT( INT, CONVERT( VARBINARY, @hex ) )
So, I would say the error must be somewhere here
DECLARE @hex varchar(10)
SET @hex = '0x8A'
Solution?
No, not yet!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Hi guarddata,quote:
I'd be interested to see this one. When I use SELECT CONVERT( INT, CONVERT( VARBINARY, '0x8A' ) ) the answer comes 813185089 instead of the 138 I had hoped for. What did you use to get the correct VARBINARY value?to a certain point you're right. But I think the problem is something else
DECLARE @hex varchar(10)
SET @hex = '0x8A'
SELECT CONVERT(varbinary,@hex)
you should receive 0x30783841
Now recalculating 30783841 to decimal obvious is 813185089 and that's what you (and I) get.
SELECT CONVERT( INT, CONVERT( VARBINARY, @hex ) )
So, I would say the error must be somewhere here
DECLARE @hex varchar(10)
SET @hex = '0x8A'
Solution?
No, not yet!
Cheers,
Frank
actially when you place the whole stuff into the dynamic statement I've mentioned in my first post it seems to work
declare @hex varchar(10)
DECLARE @stmt nvarchar(255)
DECLARE @int int
set @hex = '0x8A'
SELECT @stmt = N'Select @int = convert( int, CONVERT(VARBINARY, ' + @hex + ' ))'
EXEC sp_ExecuteSql @stmt, N'@int Int Out', @int OUT
SELECT @int
GO
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just for completeness,
there is an undocumented extended stored procedure converting hex to int
DECLARE @dec varbinary(10)
DECLARE @result varchar(255)
SET @dec = 813185089
EXEC master..xp_varbintohexstr @dec, @result OUT
PRINT @resultCheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nice one Frank, wish I had known that a while back. Would have saved me a lot of trouble from using datatype conversions and case statement.
Addendum
Found sp_hexadecimal that does the same
Edited by - davidburrows on 07/23/2003 07:00:33 AM
Far away is close at hand in the images of elsewhere.
Anon. -
OK - so overcoming the nausia of having the output be different when put into dynamic SQL
- which does, indeed, bother me still... How do you wrap this one to read from a table rather than working on one value at a time?Timingskey - you seemed to indicate that it was working for you. What did you do?
Guarddata-
-
Hi David,
quote:
Found sp_hexadecimal that does the samewhere is this sp?
Can't seem to find it!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
From google groups:
CREATE FUNCTION dbo.ConvertHexToInt (@inputstring varchar(20))
RETURNS int AS
BEGIN
DECLARE @digit as int;
DECLARE @result as int;
DECLARE @index as int;
DECLARE @length as int;
DECLARE @convstr as char(16);
SET @result = 0;
SET @convstr = '123456789ABCDEF';
SET @index = len(@inputstring);
SET @length = len(@inputstring);
WHILE (@index > 0)
BEGIN
SET @digit = charindex(substring(@inputstring, @index, 1), @convstr)
SET @result = @result + @digit * power(16, (@length - @index))
END
RETURN @result;
END
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
- which does, indeed, bother me still... How do you wrap this one to read from a table rather than working on one value at a time?Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply