Converting hex to int

Question

Converting hex to int

timingskey

SSCommitted

Points: 1708
More actions
July 21, 2003 at 2:42 pm

#106117

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!

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

timingskey SSCommitted Points: 1708 More actions · Answer 2

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?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

timingskey SSCommitted Points: 1708 More actions · Answer 4

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

timingskey SSCommitted Points: 1708 More actions · Answer 6

Thank you so much! Converting to varbinary and then to int worked.

guarddata SSCertifiable Points: 6583 More actions · Answer 7

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-

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

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 @result

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65144 More actions · Answer 11

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.

guarddata SSCertifiable Points: 6583 More actions · Answer 12

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-

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

Hi David,

quote:
Found sp_hexadecimal that does the same

where 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]

ckempste SSCoach Points: 17983 More actions · Answer 14

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))

SET @index = @index - 1

END

RETURN @result;

END

Chris Kempster

http://www.chriskempster.com

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"

Converting hex to int

Cookies on SQLServerCentral