Converting hex to int

Question

Post reply

Converting hex to int

Viewing 12 posts - 16 through 26 (of 26 total)

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

David Burrows SSC Guru Points: 65138 More actions · Answer 1

Hi Frank,

In Master. I'm on SQL7 SP4 may be it has been dropped in later versions!

Far away is close at hand in the images of elsewhere.
Anon.

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

quote:
In Master. I'm on SQL7 SP4 may be it has been dropped in later versions!

Same as my test server, but I can't find it there.

Can this depend on the edition? SQL7 here I think is standard edition.

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: 65138 More actions · Answer 3

I'm on Standard Edition. Check one of our live servers and surprise, surprise it's not there. Only exists in our dev box. Don't remember putting it there

Sorry for the confusion. If your interested this is the proc

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(255),

@hexvalue varchar(255) OUTPUT

AS

DECLARE @charvalue varchar(255)

DECLARE @counter int

DECLARE @length int

DECLARE @hexstring char(16)

SET @charvalue = '0x'

SET @counter = 1

SET @length = DATALENGTH(@binvalue)

SET @hexstring = '0123456789ABCDEF'

WHILE (@counter <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SET @tempint = CONVERT(int, SUBSTRING(@binvalue,@counter,1))

SET @firstint = FLOOR(@tempint/16)

SET @secondint = @tempint - (@firstint*16)

SET @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)

SET @counter = @counter + 1

END

SET @hexvalue = @charvalue

Far away is close at hand in the images of elsewhere.
Anon.

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

quote:
I'm on Standard Edition. Check one of our live servers and surprise, surprise it's not there. Only exists in our dev box. Don't remember putting it there
Sorry for the confusion. If your interested this is the proc

puh, it's good to see that this time I was not missing something.

Thanks for the code!

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 5

Guarddata,

It didn't work for me. I thought it did, but then I realized that it returned the wrong value.

Timingskey

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

Hi Timingskey,

quote:
Guarddata,
It didn't work for me. I thought it did, but then I realized that it returned the wrong value.

so the dynamic statement is an option for you?

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: 65138 More actions · Answer 7

When you CONVERT(VARBINARY, '0x8A')

sql converts the chars to hex (0x30783841)

30 (0) 78 (x) 38 (8) 41 (A)

When you CONVERT(VARBINARY, 0x8A)

sql converts the value 0x8A to hex (0x8A)

That is why when these are converted to int one works the other does'nt.

I think Franks dynamic sql solution is the only way unless you write a function to manually do the conversion.

Far away is close at hand in the images of elsewhere.
Anon.

guarddata SSCertifiable Points: 6583 More actions · Answer 8

OK - So I attempted to create a function to perform this operation in order to select from a table and convert as part of a recordset output. The function compiles fine - but then comes the message "Only functions and extended stored procedures can be executed from within a function." when attempting to use it.

Suggestions?

Guarddata-

timingskey SSCommitted Points: 1708 More actions · Answer 9

Here's the function that I have to do the conversion:

CREATE FUNCTION dbo.fnHexToInt (@Hex varchar(8))

RETURNS int

-- Ver. 1.0.0 28.Oct.2001, Ofer Bester

AS BEGIN

DECLARE @i tinyint,

@Nibble tinyint,

@ch char(1),

@Result int

SET @i = 1 -- Init nibble counter

SET @Result = 0 -- Init output parameter

SET @Hex = UPPER( LTRIM( RTRIM( @Hex ) ) ) -- Convert to uppercase

WHILE (@i <= LEN(@Hex))

BEGIN

SET @ch = SUBSTRING(@Hex, @i, 1)

IF (@ch >= '0' AND @ch <= '9') SET @Nibble = ASCII(@ch) - ASCII('0')

ELSE IF (@ch >= 'A' AND @ch <= 'F') SET @Nibble = ASCII(@ch) - ASCII('A') +10

ELSE RETURN NULL

IF( @Result > 0x7FFFFFF) -- 134217727 = 0x7FFFFFF

BEGIN

SET @Result = @Result & 0x7FFFFFF -- Set MSB, of 7 nibbles, OFF

SET @Result = @Result * 16 + @Nibble +0x80000000 -- Shift left 4Bits, Add last nibble and convert to negetive number.

END

ELSE BEGIN

SET @Result = @Result *16 +@Nibble -- Shift left 4Bits, Add nibble.

END

SET @i = @i +1 -- Next nibble.

END -- While

RETURN ( @Result )

END -- Function

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

ckempste SSCoach Points: 17983 More actions · Answer 10

Hi

Did you try the function I submitted?? damn site simpler! 🙂

This was a port from an old classic C function

Cheers

Ck

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"

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

Hi Chris,

quote:
Did you try the function I submitted?? damn site simpler! 🙂
This was a port from an old classic C function

thanks, it looks familiar to me, but couldn't classify it till your post.

BTW, Yahoo Groups ????

Any useful link you can provide?

Cheers,

Frank

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

guarddata SSCertifiable Points: 6583 More actions · Answer 12

Guess I was asleep when I looked last time. Chris, David, timingskey - thanks to each of you. Plenty of options now.

Guarddata-