SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Easiest way to convert an integer into a hexadecim
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnsql
Posting Yak Master

USA
161 Posts

Posted - 08/15/2007 :  04:00:15  Show Profile  Reply with Quote
Hi,
I would like to find out an easiest way to convert an integer (maximum 8 bits in binary) into a hexadecimal presentation using SQL Server scripts. For example,
7 (int) --> E0 (hexadecimal presentation)
15 (int) --> F0 (hexadecimal presentation)
1 (int) --> 80 (hexadecimal presentation)
66 (int)--> 42 (hexadecimal presentation)
etc.

Business rule details:
Example 1: 7 (int) = 1110 0000 (8 bit binary presentation) --> E0 (hexadecimal presentation) because
4 left most bits of 1110: 1*(2^3) + 1*(2^2) + 1*(2^1) + 0*(2^1) = 14 (int) = E (hexadecimal)
4 right most bits of 0000: 0*(2^3) + 0*(2^2) + 0*(2^1) + 0*(2^1) = 0 (int) = 0 (hexadecimal)
Finally, 7 (int) --> E0 (hexadecimal presentation).

Example 2: 66 (int) = 0100 0010 (8 bit binary presentation) --> 42 (hexadecimal presentation) because
4 left most bits of 0100: 0*(2^3) + 1*(2^2) + 0*(2^1) + 0*(2^1) = 4 (int) = 4 (hexadecimal)
4 right most bits of 0010: 0*(2^3) + 0*(2^2) + 1*(2^1) + 0*(2^1) = 2 (int) = 2 (hexadecimal)
Finally, 66 (int) --> 42 (hexadecimal presentation)

Note: 2^3 means 2 to the power of 3 or 8

Thanks in advance.
johnsql

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/15/2007 :  07:53:55  Show Profile  Reply with Quote
This should get you started,

CREATE FUNCTION [dbo].[fn_decimalToBase36] (@decimal BIGINT)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @charSet CHAR(36)
SET @charSet = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

DECLARE @remainder TINYINT
DECLARE @answer VARCHAR(50)
SET @answer = ''

WHILE @decimal > 0
BEGIN
SET @remainder = @decimal % 36;
SET @answer = SUBSTRING(@charSet,@remainder+1,1) + @answer;
SET @decimal = (@decimal - @remainder) / 36;
END

RETURN @answer
END

Hope it helps,

Jim
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 08/15/2007 :  10:54:26  Show Profile  Reply with Quote
jimf,
Great solution. I like it. However, conver to hexadecimal base (16) not base 36. So, i need to modify the function for some changes as follows.

DECLARE @charSet CHAR(16)
SET @charSet = '0123456789ABCDEF'

DECLARE @remainder TINYINT
DECLARE @answer VARCHAR(50)
SET @answer = ''

WHILE @decimal > 0
BEGIN
SET @remainder = @decimal % 16;
SET @answer = SUBSTRING(@charSet,@remainder+1,1) + @answer;
SET @decimal = (@decimal - @remainder) / 16;
END
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 08/15/2007 :  10:58:36  Show Profile  Reply with Quote
Well,
I try converting 7 decimal to hexadecimal, the solution fails to convert it to E0 in hexadecimal!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/15/2007 :  18:04:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ALTER FUNCTION	[dbo].[fnNumber2AnyBase]
(
	@Number BIGINT,
	@Base TINYINT
)
RETURNS VARCHAR(64)
AS

BEGIN
	DECLARE	@Digits CHAR(36),
			@Length SMALLINT,
			@Result VARCHAR(64),
			@Divisor BIGINT

	IF @Base > 36 OR @Base < 2 OR @Base IS NULL
		RETURN ''

	SELECT	@Digits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
			@Length = 1 + CEILING(LOG(@Number) / LOG(@Base)),
			@Result = '',
			@Divisor = POWER(1.0 * @Base, 1.0 * @Length)

 	WHILE @Length >= 0
 		SELECT	@Result = @Result + SUBSTRING(@Digits, 1 + (@Number / @Divisor) % @Base, 1),
				@Divisor = @Divisor / @Base,
	 			@Length = @Length - 1

	RETURN SUBSTRING(@Result, PATINDEX('%[^0]%', @Result), 64)
END



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/15/2007 :  18:09:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why do you have the bits from left to right, instead of normal right to left?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 08/16/2007 :  08:30:02  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Why do you have the bits from left to right, instead of normal right to left?

Peso,
Thanks for your posts. That is a project requirements and I do not know how to explain.
johnsql


E 12?55'05.25"
N 56?04'39.16"

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000