Integer to Hex Convertion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Integer to Hex Convertion

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-06-14 : 11:15:19

Hi All,

I need to convert some data from int to Hexadecimal value.
I looked through the topics in the forum, but didn't find anything.
Does anybody have any suggestions.

TIA.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 11:17:59

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61914
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65364

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-06-14 : 13:24:17

Khtan,
thanks for the link, but I have problems with that function.
When I try to create the function I got an error:
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master..xp_varbintohexstr'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
Any suggestions.
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 19:31:42

xp_varbintohexstr is an extended procedure not a table. It is in 2000. You are using 2005 ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-14 : 20:11:38

A simple google for "sql server convert int to hexadecimal" gives plenty of solutions. this one seemed the easiest:

http://www.codeproject.com/database/ConvertHexToIntUsingSQL.asp

- Jeff
http://weblogs.sqlteam.com/JeffS

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-06-15 : 14:31:18

Thanks guys, I found a universal solution.

Create function f_Convert_From_Base10 (@num int, @base int)
returns varchar(255)
AS
begin

declare @string varchar(255)
declare @return varchar(255)
declare @finished bit
declare @div int
declare @rem int
declare @char char(1)

select @string = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @return = case when @num <= 0 then '0' else '' end
select @finished = case when @num <= 0 then 1 else 0 end
select @base = case when @base < 2 OR @base IS NULL then 2 when @base > 36 then 36 else @base end

while @finished = 0
begin
select @div = @num / @base
select @rem = @num - (@div * @base)
select @char = SUBSTRING(@string, @rem + 1, 1)
select @return = @char + @return
select @num = @div

if @num = 0 select @finished = 1
end
select @return = cast(replicate('0', 8 - len(@return)) + @return as varchar(8))
return @return

end
go

Looks pretty straight-forward, doesn't it?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 15:24:29

If you just want a simple one that converts to HEX:


create function ToHex(@i int)
returns varchar(10)
as
	begin

	declare @ret varchar(8)
	declare @c char(16), @x int
	select @c= '0123456789ABCDEF', @ret='', @x=7

	while (@x >= 0)
		select @ret = @ret + substring(@c, (@i/power(16,@x)) % 16 + 1,1), @x=@x-1
	
	return '0x' + @ret 
end

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources