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.
Author |
Topic |
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-08-15 : 04:00:15
|
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 8Thanks in advance.johnsql |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-15 : 07:53:55
|
This should get you started,CREATE FUNCTION [dbo].[fn_decimalToBase36] (@decimal BIGINT)RETURNS VARCHAR(50)ASBEGIN 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 @answerENDHope it helps,Jim |
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-08-15 : 10:54:26
|
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 TINYINTDECLARE @answer VARCHAR(50)SET @answer = ''WHILE @decimal > 0BEGIN SET @remainder = @decimal % 16; SET @answer = SUBSTRING(@charSet,@remainder+1,1) + @answer; SET @decimal = (@decimal - @remainder) / 16;END |
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-08-15 : 10:58:36
|
Well, I try converting 7 decimal to hexadecimal, the solution fails to convert it to E0 in hexadecimal! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-15 : 18:04:40
|
[code]ALTER FUNCTION [dbo].[fnNumber2AnyBase]( @Number BIGINT, @Base TINYINT)RETURNS VARCHAR(64)ASBEGIN 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[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-15 : 18:09:05
|
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" |
|
|
johnsql
Posting Yak Master
161 Posts |
Posted - 2007-08-16 : 08:30:02
|
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"
|
|
|
|
|
|
|
|