People are accustomed to using a decimal base to work with numbers. However, in computerized systems, it sometimes makes more sense to use other bases, which can let you employ fewer digits to represent numbers. In my June column, "Performing Base Conversions," InstantDoc ID 46016, I discussed base conversions and demonstrated some techniques to convert a value expressed in any given base to a decimal value. I explained how you can perform base-to-decimal conversions efficiently by using set-based techniques. The solutions I described require a lot of logical manipulation. Likewise, by using mainly logical deduction, you can figure out how to convert values back from a decimal base to another base. By using the techniques I showed last month and the ones I'll describe this month, you can convert numbers from any source base to any target base. Don't forget to check the "Logical Puzzle" sidebar, page XX, for the solution to last month's Burning Ropes puzzle and to try your luck with a new puzzle—calculating a maximum value by using a mathematical expression.
Converting to Decimal
If applications need to compress numbers into fewer digits than decimal values can store, they can feed nondecimal-base values to SQL Server for storage. For example, serial numbers in base 36 require significantly fewer digits to represent values than in decimal base. The decimal value 3792919053113386084 has 19 digits, but the same value expressed in base 36 requires only 12 digits: STELLAARTOIS. Such nondecimal numbers might represent serial numbers printed or burned onto products in a limited amount of space.
SQL Server lacks built-in support for representing values in nondecimal bases, so you usually store nondecimal-base values in your database as character strings. If you need to perform arithmetic manipulation between nondecimal-base values, you can convert them to decimal values first, perform the arithmetic manipulation, then convert the result back to the original base.
In June, I explained how to convert a value in a given base to a decimal value. Now, let's look at how to do the opposite. Given a decimal value v and a target base b, generate a character string representing v in base b. To obtain test data, run the script in Listing 1, which creates the T1 table and populates it with three decimal values: 3792919053113386084, 31, and 11. You need to develop T-SQL code that returns all decimal values from T1 in a requested target base (@base).
To validate your solutions, run them for bases 36, 16, and 2. Table 1 shows the desired results for base 36, Table 2 for base 16, and Table 3 for base 2. I'll present three solutions, but since this problem is an exercise in logic, try devising your own solutions before looking at mine.
Iterative Solution
I'll start with the iterative solution because it's the simplest and most straightforward of the three. I created a function called fn_dectobase(), which accepts two inputs: @val, the input decimal value, and @base, the target base. The function returns a character string that represents the input value in the target base.
The function implements the following (pseudo code) algorithm:
declare and init @r with empty string;
while @val > 0
begin
@r = (new_base_digit
representing @val mod
@base) + @r;
-- + used to concatenate
@val = @val div @base;
end
return @r;
Essentially, the function iteratively extracts the rightmost digit from @val by performing @val mod @base, converts the decimal value to the new base digit, and concatenates it with the result string. The function then removes the digit from @val by performing @val div @base.
For example, suppose you want to convert the decimal value 31 to base 16. The aforementioned algorithm applies the following series of steps (pseudo code):
@val = 31; @base = 16; @r = ';
@r = base16digit
(31 mod 16) + @r;
-- base16digit(15) + ' → 'F'
+ ' → 'F'
@val = 31 div 16; -- 1
@r = base16digit(1 mod 16) + @r;
-- base16digit(1) + 'F' → '1'
+ 'F' → '1F'
@val = 1 div 16; -- 0
The result is 1F. The base16 digit(<decimal_value>) in the pseudo code stands for the calculation of a single digit in the new base (16 in this example). To find the new base digit that represents a certain decimal value, use the following T-SQL expression:
SUBSTRING('0123456789ABCDEFGHIJKL
MNOPQRSTUVWXYZ',
<decimal_value> + 1, 1)
The first argument to the SUBSTRING() function is a string containing a sequence of 36 digits supporting any base up to 36. Given a decimal value n, where n < target_base, the corresponding target_base digit is the digit in the n + 1 position within the string. If you wonder why this is true, simply imagine that you had 36 fingers instead of 10 and used those to count. For example, the decimal value n is represented by the base 16 digit F, which appears in the sixteenth position (15 + 1) within the string.
Running the code in Listing 2 creates the fn_dectobase() function, which implements the algorithm I described earlier. To test the function, run the following code:
SELECT dbo.fn_dectobase(31, 16);
You get the string "1F" as a result. To get the results shown in Tables 1, 2, and 3, run the queries that Listing 3 shows.
Prev. page
[1]
2
next page 
