How to convert decimal to hex in sql?

Question

Post reply

How to convert decimal to hex in sql?

Jims

Old Hand

Points: 366
More actions
January 14, 2004 at 5:25 pm

#358917

Hello - does anyone know how to convert a decimal value to a decimal value in SQL?
Thanks,
Jim

Viewing 10 posts - 1 through 9 (of 9 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Found from one of KB.

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar(256) OUTPUT

AS

DECLARE @charvalue varchar(256)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE @tempint int

DECLARE @firstint int

DECLARE @secondint int

SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT @firstint = FLOOR(@tempint/16)

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

SELECT @charvalue = @charvalue +

SUBSTRING(@hexstring, @firstint+1, 1) +

SUBSTRING(@hexstring, @secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

declare @hexvalue1 varchar(256)

exec sp_hexadecimal @binvalue = 15, @hexvalue = @hexvalue1 output

print @hexvalue1

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

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

DECLARE @i INT

DECLARE @vNIL INT

DECLARE @szString VARCHAR(255)

SET @i=35

SET @szString=''

WHILE (@i>0)

BEGIN

SET @vNIL=@i % 16

SET @i=@i /16

IF @vNIL>9

SET @szString=char(55+@vNIL)+@szString

ELSE

SET @szString=char(48+@vNIL)+@szString

END

SELECT @szString

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I suggest doing this at the client, because for almost every programming language this is a far more easy exercise.

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

Jims Old Hand Points: 366 More actions · Answer 3

Thanks Allen and Frank, your posts are very helpful.

Jim

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

To convert an integer to an actual hexadecimal value you can just:

DECLARE @i int

SET @i = 1234

SELECT CAST(@i AS varbinary)

To display it as a string, I usually cheat and use an undocumented system function:

SELECT master.dbo.fn_varbintohexstr(CAST(@i AS varbinary))

--Jonathan

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Allen Cui-55137

SSC Guru

Points: 51650

January 15, 2004 at 10:43 am

#490254

Good to know the simpliest way to do it.

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

Yes, I also thought of this, but IIRC you need to be on SQL 2k to have it available, right?

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

Well, it is a UDF...

Can't we assume everyone's upgraded by now? It's been three and a half years...

--Jonathan

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

You mean an XP?

Well, actually our developers who code app to work with the data from one of our bank partners is bound by the bank to use VB5 and SQL 6.5 !!!

So far for the technological revolutionary mind of bankers

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

Answering my own question.

There is a UDF and an XP. Wow, that's what I call luxury.

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