SQL Challenge - convert to hex 4

[ESquared]

Challenge: write a user-defined function that accepts positive integers as large as decimal(32,0) and returns the hexadecimal expansion for that value. (Decimal(38, 0) is difficult because then you can't do any division at all on it.) You may break it out into multiple functions if you like.

For example, here are the outputs from my function for two inputs:

PRINT dbo.NumberToHex(65535) -- 0000FFFF
PRINT dbo.NumberToHex(convert(decimal(32,0), 99999999999999999999999999999999)) -- 00000004EE2D6D415B85ACEF80FFFFFFFF

I'll be flexible for now and not pay attention to how many leading zeroes your function does or does not add. At least one digit must be returned.

Entries will be judged in the following categories:

• Shortest code, not counting line breaks
• Fastest code overall, 50000 or more iterations each of random values between 0 and various limits from 0 up to the large all-9s number above (generating a curve)
• Style, as judged by me and influenced by comments from participants
• Bonus: entries which return a number of leading zeroes relevant to the type of the input variable (see that my first example is eight hex digits long with four leading zeroes).

I have already written function testing code which allows me to specify as many functions as I like and as many input values as I like, and run through them spitting out elapsed time for each. The speed results will go into a spreadsheet and be graphed.

I wrote a version of this function about a year ago, and today I have been tweaking it. I'm almost done (and I won't look at any entries here until it's finished).

I hope we have some good fun with this! If people like it, I'll do more of them. Kind of a more involved SQL teaser.

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[ESquared]

Okay, I finally have my submission for this challenge. I'm sorry about the long delay in completing it! I've been busy!

-- Converts a number to its hex string representation (number must be able to be converted to decimal(38, 0))
CREATE FUNCTION NumberToHexErik (@Number decimal(38, 0))
RETURNS varchar(32)
AS
BEGIN
   DECLARE
      @Hex varchar(32),
      @Digits char(16),
      @Temp decimal(38, 0),
      @Val varchar(16),
      @Pos int,
      @Asc tinyint
   SET @Digits = '0123456789ABCDEF'
   SET @Hex = ''
   WHILE @Number > 0 BEGIN
      IF @Number > 72057594037927936 BEGIN
         SET @Temp = Floor(@Number / 72057594037927936)
         SET @Val = Convert(varbinary(7), Convert(bigint, @Number - @Temp * 72057594037927936))
      END
      ELSE BEGIN
         SET @Temp = 0
         SET @Val = Convert(varbinary(7), Convert(bigint, @Number))
      END
      SET @Pos = 7
      WHILE @Pos > 0 BEGIN
         SET @Asc = Ascii(Substring(@Val, @Pos, 1))
         SET @Hex = Substring(@Digits, @Asc / 16 + 1, 1) + Substring(@Digits, @Asc % 16 + 1, 1)  + @Hex
         SET @Pos = @Pos - 1
      END
      SET @Number = @Temp
   END
   IF Len(@Hex) <= 2 RETURN Right('00' + @Hex, 2)
   RETURN Replicate('0', Len(@Hex) % 2) + @Hex
END

I will be using the following code to decide the speed of all entries:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HexLTrim]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HexLTrim]
GO
CREATE FUNCTION HexLTrim(@Hex varchar(34))
RETURNS varchar(34)
AS
BEGIN
   WHILE @Hex LIKE '00%' SET @Hex = Substring(@Hex, 3, 31)
   RETURN @Hex
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HexCompare]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HexCompare]
GO
CREATE FUNCTION HexCompare(@Hex1 varchar(34), @Hex2 varchar(34))
RETURNS bit
AS
BEGIN
   RETURN CASE WHEN dbo.HexLTrim(@Hex1) = dbo.HexLTrim(@Hex2) THEN 1 ELSE 0 END
END
GO


DECLARE
   @Time datetime,
   @Iter int,   
   @Result varchar(32),
   @Function nvarchar(4000),
   @Exec nvarchar(4000),
   @FuncNum int,
   @Value sql_variant,
   @Limit decimal(38, 0),
   @Message varchar(8000),
   @Incorrect int,
   @TempVal decimal(38, 0)
SET NOCOUNT ON
SELECT FuncNum = identity(int, 1, 1), * INTO #Functions
FROM ( -- Limit is for functions that have upper limits less than decimal(32, 0), for testing
   SELECT FunctionName = 'NumberToHexErik', Limit = Convert(decimal(38, 0), NULL)
   UNION ALL SELECT 'Function Entry 2', NULL
   UNION ALL SELECT 'Function Entry 3', NULL
-- ... more entries
   UNION ALL SELECT 'IntToHex', 9223332036854775807
) X

SELECT Value = Convert(decimal(38, 0), 255) INTO #Values
UNION ALL SELECT 65535
UNION ALL SELECT 16773215
UNION ALL SELECT 4294963295
UNION ALL SELECT 1099511627775
UNION ALL SELECT 281474976710655
UNION ALL SELECT 32057594037927935
UNION ALL SELECT 9223332036854775807
UNION ALL SELECT 2361173001434822606847
UNION ALL SELECT 604460288367314587353087
UNION ALL SELECT 154741833822032534362390527
UNION ALL SELECT 39613909458440328796771975167
UNION ALL SELECT 10141160821360724171973625643007
UNION ALL SELECT 99999999999999999999999999999999
-- UNION ALL SELECT 2596137170268345388025248164610047
-- UNION ALL SELECT 664611115588696419334463530140172287

SET @Message = ''
SET @Value = (SELECT Min(Value) FROM #Values)
WHILE @Value IS NOT NULL BEGIN
   SET @Message = @Message + Char(9) + Convert(varchar(40), @Value)
   SET @Value = (SELECT Min(Value) FROM #Values WHERE Value > @Value)
END
PRINT @Message
SELECT @FuncNum = Min(FuncNum) FROM #Functions
SELECT @Function = FunctionName, @Limit = Limit FROM #Functions WHERE FuncNum = @FuncNum
WHILE @Function IS NOT NULL BEGIN
   SET @Message = Convert(varchar(11), @FuncNum) + '-' + @Function
   SET @Value = (SELECT Min(Value) FROM #Values)
   WHILE @Value IS NOT NULL BEGIN
      IF @Limit IS NULL OR @Limit >= @Value BEGIN
         IF @Function LIKE '%int%' SET @Value = convert(bigint, @Value)
         SET @Iter = 0
         SET @Incorrect = 0
         WHILE @Iter < 1000 BEGIN -- accuracy check
            SET @TempVal = Convert(decimal(38, 0), rand() * Convert(decimal(38, 0), @Value))
            SET @Exec = 'SET @TempVal = Convert(' + dbo.VarType(@Value) + ', ' + Convert(nvarchar(40), @TempVal) + ')'
            EXEC sp_executesql @Exec, N'@TempVal decimal(38, 0) OUTPUT', @TempVal OUTPUT
            SET @Exec = N'SET @Result = dbo.' + @Function + '(Convert(' + dbo.VarType(@Value) + ', ' + Convert(varchar(40), @TempVal) + '))'
            EXEC sp_executesql @Exec, N'@Result varchar(32) OUTPUT', @Result OUTPUT
            IF dbo.HexCompare(@Result, dbo.NumberToHex(@TempVal)) = 0 BEGIN
               SET @Incorrect = @Incorrect + 1
               IF @Incorrect = 1 BEGIN
                  PRINT 'incorrect: ' + IsNull(dbo.VarType(@TempVal), 'NULL') + ' ' + IsNull(Convert(varchar(40), @TempVal), 'NULL') + ' -> "' + IsNull(@Result, 'NULL') + '" != correct result "' + IsNull(dbo.NumberToHex(@TempVal), 'NULL') + '"'
                  PRINT '  ' + @Exec
               END
            END
            SET @Iter = @Iter + 1
         END
         IF @Incorrect = 0 BEGIN -- speed check
            SET @Exec = N'SET @Result = dbo.' + @Function + '(Convert(' + dbo.VarType(@Value) + ', rand() * ' + Convert(varchar(40), @TempVal) + '))'
            SET @Iter = 0
            SET @Time = GetDate()
            WHILE @Iter < 50000 BEGIN
               EXEC sp_executesql @Exec, N'@Result varchar(32) OUTPUT', @Result OUTPUT
               SET @Iter = @Iter + 1
            END
            SET @Time = GetDate() - @Time
         END
         SET @Message = @Message + Char(9) + CASE WHEN @Incorrect = 0 THEN Convert(varchar(11), datediff(ms, 0, @Time)) ELSE ' (' + Convert(varchar(11), @Incorrect) + ' incorrect - sample ' + @Result + ')' END
      END
      ELSE BEGIN
         SET @Message = @Message + Char(9)
      END
      SET @Value = (SELECT Min(Value) FROM #Values WHERE Value > @Value)
   END
   PRINT @Message
   SET @FuncNum = @FuncNum + 1
   SET @Function = NULL
   SELECT @Function = FunctionName, @Limit = Limit FROM #Functions WHERE FuncNum = @FuncNum
END

DROP TABLE #Functions
DROP TABLE #Values
GO

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

George, I didn't use your early submission, figuring there was no point!

Alex, I wrote a function to perform the method of instantiation that you suggested for your submission. In testing it started out at about double the time of the other two submissions, and went up at a very steep angle (about 7 seconds more per 2 hex characters). I didn't have the patience to wait the ~800 seconds to let it finish.

So I rewrote your function as a standalone with no other function calls but still using recursion, and it fared no better. So I made a loop instead of recursion and pitted that against the others, and numbers dropped radically. The code is as closely yours as possible with the recursion expanded into a loop.

And... it looks like recursion is A Bad Thing in SQL Server!

Here's the final function I used:

create function NumberToHexAlex3(@dIn decimal(32,0)) 
returns varchar(100)
as
begin
	declare @result varchar(100)
	declare @cMod integer
	
   set @result = ''
	while @dIn > 0 begin
		set @cMod = @dIn - (floor(@dIn / 16) * 16)
		set @result = substring('0123456789ABCDEF', @cMod + 1, 1) + @result
		set @dIn = (@dIn-@cMod)/16
	end
	return replicate('0', 8 - (len(@result) % 8)) + ltrim(rtrim(@result))
end

And here are the results of the three submissinos:



The lines should all be smoother than they are. Every time I run this some points move a little, up or down (probably due to the random numbers used) but they stay consistently in a certain range. If I plot the same thing multiple times, I can see how the "real" values are smoothed. My function has some zig zags, but in reality it should be two nearly straight lines joined by one jog in the middle. George's function shouldn't shoot up at the end. But it gives you an idea of the performance and how they stack up!

• Shortest code, not counting line breaks
- George

• Fastest code overall, 50000 or more iterations each of random values between 0 and various limits from 0 up to the large all-9s number above (generating a curve)
- Erik

• Style, as judged by me and influenced by comments from participants
- Alex did some nice reusable functions, obviously thinking about separating the work into identifiable chunks. Recursion is an elegant way to easily get work done but it has a high overhead. If this was Lisp he'd have been fine (the only loops in Lisp are via recursion).
- Erik forgot he could just substring against a string literal, DOH. But he cut execution time by exploiting conversion to varbinary even for decimal or large numbers.
- George had nice short code that was the fastest of the most popular method used (including my early function). Both he and Alex used SELECT instead of SET which is against the recommendations of BOL.

• Bonus: entries which return a number of leading zeroes relevant to the type of the input variable (see that my first example is eight hex digits long with four leading zeroes).

We dropped this requirement. Especially using the varbinary method it became hard to do what I really wanted, which was to always use a number of hex digits that was a power of two: 2, 4, 8, 16 or 32 digits. An early function of mine did that this way:

IF Len(@Hex) <= 2 RETURN Right('00' + @Hex, 2)
RETURN Replicate('0', Power(2, Ceiling(Log(Len(@Hex)) / 0.69314718055994529)) - Len(@Hex)) + @Hex

Note that the 0.69... number in there is the natural log of 2.

So who wins over all?

In a production environment, performance is king. But development time is a reality. As the challenge presenter, choosing myself had better be clearly right, and given it took me many days to come up with my entry, I call it a tie between me and George. But NEXT time I have to do something like this I'll think more about exploiting built-in SQL Server operations like my final submission did.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[gmmastros]

Erik,

The image you posted is completely unreadable. Can you please post a larger version of this?

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

Nevermind. That image link doesn't want to open with IE but appears to be ok with firefox.

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

It works okay for me with IE 6, but if that image hosting service isn't good for these forums I'll find another one!

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

And to be fair, Alex had a function almost identical to George's so he didn't submit that and worked on something different instead.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[gmmastros]

Can you post the dbo.VarType function? What is it supposed to do?

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

Oh, I forgot to post it! It's a super helpful UDF I wrote that I use quite frequently. It simply spits out a text string containing the SQL datatype definition of an input. It's usually much faster to get the information I need than sticking something in a table just to read the data type.

Note that for varchar data types it simply gives you the length of the string you pass in. Not sure about varbinary. I use it most for examining numeric data types...

CREATE FUNCTION VarType(@Variable sql_variant)
RETURNS varchar(256)
AS
BEGIN
   DECLARE @VarType varchar(256)
   SET @VarType = Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType'))
   IF @VarType LIKE '%char%' OR @VarType LIKE '%binary%'
      RETURN @VarType + '(' + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'MaxLength')) + ')'

   IF @VarType IN ('decimal', 'money', 'numeric')
      SET @VarType = @VarType + '('
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Precision'))
         + ','
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Scale'))
         + ')'
   RETURN @VarType
END

I also have another version but I haven't decided which is more efficient:

CREATE FUNCTION VarType2 (@Variable sql_variant)
RETURNS varchar(256)
AS
BEGIN
   RETURN
      Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType'))
      + CASE
      WHEN Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType')) LIKE '%char%' OR Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType')) LIKE '%binary%'
         THEN '(' + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'MaxLength')) + ')'
      WHEN Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType')) IN ('decimal', 'money', 'numeric') 
         THEN '('
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Precision'))
         + ','
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Scale'))
         + ')'
      ELSE '' END
END

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[onpnt]

Alex have a * for posting the CLR. One day some of us will adopt this new found glory and realize in many situations it far exceeds the abilities and performance of T-SQL on the database level

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}