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.

 

[gmmastros]

Can I suggest that we add 0x to the beginning of the output, so that 65535 becomes [!]0x[/!]0000FFFF

Spoiler

Select Convert(VarBinary, 65535)



** I intentionally left some blank
lines in here so that it would
appear to be more code.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Doesn't work for 99999999999999999999999999999999 George, I thought the same last night but 99999999999999999999999999999999 will return 0x20000001FFFFFFFF80EFAC855B416D2DEE040000

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[AlexCuse]

Oooh this sounds like fun. I will see if I can throw something together today

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

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

I like this, helps me learn. I'm going to see what I can do.

<.

 

[gmmastros]

Denis,

I noticed that shortly after posting. Here's what I think is going on....

I think the built-in convert function will return different values depending on the data type of the variable passed in. Since I didn't use a variable, SQL Server interprets the value based on the data type order of precedence. So, when the number fits nicely in an integer, that's what it converts to. If the number is too large for an integer, it converts it to a decimal, and the convert function returns a Decimal -> hex conversion, which is different than an integer -> hex conversion

Ex:

[COLOR=blue]Declare[/color] @intValue [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @decValue [COLOR=blue]Decimal[/color](32,0)

[COLOR=blue]Set[/color] @IntValue = 65
[COLOR=blue]Set[/color] @decValue = 65

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]varbinary[/color], @intValue), [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarBinary[/color], @decValue)

Anyway... In the spirit of, "First make it work, then make it better"... Here's my first attempt.

Spoiler

[tt]
Alter Function dbo.NumberToHex
(@Temp Decimal(32,0))
Returns
VarChar(100)
As
Begin
Declare @Bin VarChar(1000)

Set @Bin = ''
While @Temp >= 1
Begin
Select @Bin = Convert(Char(1), Convert(Int, Right(ParseName(@Temp / 1, 2), 1)) % 2) + @Bin
Set @Temp = Floor(@Temp / 2)
End

Set @Bin = Replicate('0', 4 - Len(@Bin) % 4) + @Bin

Declare @Output VarChar(100)
Set @Output = ''
While Len(@Bin) > 0
Begin
Select @Output = Case Right(@Bin, 4)
When '0000' Then '0'
When '0001' Then '1'
When '0010' Then '2'
When '0011' Then '3'
When '0100' Then '4'
When '0101' Then '5'
When '0110' Then '6'
When '0111' Then '7'
When '1000' Then '8'
When '1001' Then '9'
When '1010' Then 'A'
When '1011' Then 'B'
When '1100' Then 'C'
When '1101' Then 'D'
When '1110' Then 'E'
When '1111' Then 'F'
End + @Output

Set @Bin = Left(@Bin, Len(@Bin) - 4)
End

Set @Output = Replicate('0', 8 - Len(@Output) % 8) + @Output
Return @Output
End
[/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ESquared]

Can I suggest that we add 0x to the beginning of the output, so that 65535 becomes 0x0000FFFF

You can suggest, but adding 0x doesn't abide by the terms of the challenge. However, I'll be nice and remove or add code for any entries like this.

The reason is that this function is not just a random mathematical exercise but was something I needed in production (for some reason I can't remember now) and the '0x' part is only for human readability. The code consuming my function results already knew it was getting hex, and would have had to strip off the '0x' to do its job.

If I was just looking to humanly know the hex equivalent of a number, I'd just convert it to varbinary!

--------------------

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[AlexCuse]

How about this? I still need to work out the padding of the return values:

[COLOR=green]--main function
[/color][COLOR=blue]alter[/color] [COLOR=#FF00FF]function[/color] toHex(@dIn [COLOR=blue]decimal[/color](32,0)) 
returns [COLOR=blue]varchar[/color](100)
[COLOR=blue]as[/color]

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @result [COLOR=blue]varchar[/color](100)
[COLOR=blue]declare[/color] @cMod [COLOR=blue]integer[/color]

[COLOR=blue]select[/color] @cMod = dbo.ModuloDec(@dIn, 16)
 
[COLOR=blue]if[/color](@dIn-@cMod=0) 
    [COLOR=blue]select[/color] @result = dbo.HexChar(@cMod)
[COLOR=blue]else[/color] 
        [COLOR=blue]select[/color] @result = dbo.toHex( (@dIn-@cMod)/16 )+dbo.HexChar(@cMod)

[COLOR=blue]return[/color] @result

[COLOR=blue]end[/color]

[COLOR=blue]go[/color]

[COLOR=green]--function to return appropriate hex character
[/color][COLOR=blue]alter[/color] [COLOR=#FF00FF]function[/color] HexChar(@n [COLOR=blue]int[/color])
returns [COLOR=blue]char[/color](1) [COLOR=blue]as[/color]
[COLOR=blue]begin[/color]
    [COLOR=blue]declare[/color] @retChars [COLOR=blue]char[/color](16)
    [COLOR=blue]set[/color] @retChars = [COLOR=red]'0123456789ABCDEF'[/color]
    [COLOR=blue]return[/color] [COLOR=#FF00FF]substring[/color](@retChars, @n + 1, 1)
[COLOR=blue]end[/color]


[COLOR=blue]go[/color]

[COLOR=green]--modulus replacement
[/color][COLOR=blue]alter[/color] [COLOR=#FF00FF]function[/color] ModuloDec(@n [COLOR=blue]decimal[/color](32,0), @mWhat [COLOR=blue]int[/color])
returns [COLOR=blue]int[/color]
[COLOR=blue]as[/color]
[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @mod [COLOR=blue]int[/color]
[COLOR=blue]select[/color] @mod = @n - ([COLOR=#FF00FF]floor[/color](@n / @mWhat) * @mWhat)
[COLOR=blue]return[/color] @mod

[COLOR=blue]end[/color]

[COLOR=blue]go[/color]

[COLOR=green]--padding function
[/color][COLOR=blue]alter[/color] [COLOR=#FF00FF]function[/color] padHex (@hex [COLOR=blue]varchar[/color](100))
returns [COLOR=blue]varchar[/color](108)
[COLOR=blue]as[/color]
[COLOR=blue]begin[/color]

[COLOR=blue]return[/color] [COLOR=#FF00FF]replicate[/color]([COLOR=red]'0'[/color], 8 - (len(@hex) % 8)) + [COLOR=#FF00FF]ltrim[/color]([COLOR=#FF00FF]rtrim[/color](@hex))

[COLOR=blue]end[/color]

For now, call 'em like this:

[COLOR=blue]select[/color] dbo.padHex(dbo.toHex([COLOR=#FF00FF]cast[/color](99999999999999999999999999999999 [COLOR=blue]as[/color] [COLOR=blue]decimal[/color](32,0))))
[COLOR=blue]select[/color] dbo.padHex(dbo.toHex([COLOR=#FF00FF]cast[/color](65535 [COLOR=blue]as[/color] [COLOR=blue]decimal[/color](32,0))))

I should have the padding worked out soon, but I need to do some work first.

Alex





Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

That's not bad Alex, It's like this sp_ This is used by sp_help_revlogins.

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] sp_hexadecimal
    @binvalue [COLOR=blue]varbinary[/color](256),
    @hexvalue [COLOR=blue]varchar[/color](256) [COLOR=blue]OUTPUT[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]DECLARE[/color] @charvalue [COLOR=blue]varchar[/color](256)
[COLOR=blue]DECLARE[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]DECLARE[/color] @length [COLOR=blue]int[/color]
[COLOR=blue]DECLARE[/color] @hexstring [COLOR=blue]char[/color](16)
[COLOR=blue]SELECT[/color] @charvalue = [COLOR=red]'0x'[/color]
[COLOR=blue]SELECT[/color] @i = 1
[COLOR=blue]SELECT[/color] @length = [COLOR=#FF00FF]DATALENGTH[/color] (@binvalue)
[COLOR=blue]SELECT[/color] @hexstring = [COLOR=red]'0123456789ABCDEF'[/color] 
[COLOR=blue]WHILE[/color] (@i <= @length) 
[COLOR=blue]BEGIN[/color]
  [COLOR=blue]DECLARE[/color] @tempint [COLOR=blue]int[/color]
  [COLOR=blue]DECLARE[/color] @firstint [COLOR=blue]int[/color]
  [COLOR=blue]DECLARE[/color] @secondint [COLOR=blue]int[/color]
  [COLOR=blue]SELECT[/color] @tempint = [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]int[/color], [COLOR=#FF00FF]SUBSTRING[/color](@binvalue,@i,1))
  [COLOR=blue]SELECT[/color] @firstint = [COLOR=#FF00FF]FLOOR[/color](@tempint/16)
  [COLOR=blue]SELECT[/color] @secondint = @tempint - (@firstint*16)
  [COLOR=blue]SELECT[/color] @charvalue = @charvalue +
    [COLOR=#FF00FF]SUBSTRING[/color](@hexstring, @firstint+1, 1) +
    [COLOR=#FF00FF]SUBSTRING[/color](@hexstring, @secondint+1, 1)
  [COLOR=blue]SELECT[/color] @i = @i + 1
[COLOR=blue]END[/color]
[COLOR=blue]SELECT[/color] @hexvalue = @charvalue

[COLOR=blue]GO[/color]

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Well, it muddies the waters a bit but I am feeling lazy:

create function toHexPad(@dIn decimal(32,0))
returns varchar(108) as
begin

return dbo.padHex(dbo.toHex(@dIn))

end

select dbo.toHexPad(cast(99999999999999999999999999999999 as decimal(32,0)))
select dbo.toHexPad(cast(65535 as decimal(32,0)))

Maybe I will think of something a bit more inspired later on ;-)

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

First make it work, right? Here's a better version.

[COLOR=blue]Alter[/color] [COLOR=#FF00FF]Function[/color] dbo.NumberToHex(@Temp [COLOR=blue]Decimal[/color](32,0))
Returns [COLOR=blue]VarChar[/color](100)
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]
  [COLOR=blue]Declare[/color] @Output [COLOR=blue]VarChar[/color](100)
  [COLOR=blue]Set[/color] @Output = [COLOR=red]''[/color]
  [COLOR=blue]While[/color] @Temp >= 1
      [COLOR=blue]Select[/color] @Output = [COLOR=#FF00FF]SubString[/color]([COLOR=red]'0123456789ABCDEF'[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Int[/color], @Temp - [COLOR=#FF00FF]Floor[/color](@Temp/16) * 16) + 1, 1) + @Output,
             @Temp = [COLOR=#FF00FF]Floor[/color](@Temp / 16)
        	
  [COLOR=blue]Set[/color] @Output = [COLOR=#FF00FF]Replicate[/color]([COLOR=red]'0'[/color], 8 - Len(@Output) % 8) + @Output

  [COLOR=blue]Return[/color] @Output
[COLOR=blue]End[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

My 'better version' is almost exactly the same as George's.

I think that the java class I am taking may be causing some brain damage ;-)

I'm gonna try CLR for this from home some time, as I think it would be pretty good use for that feature (no vs.net at work :-( )

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

After some fiddling I'm retracting the bonus: only int data types have any kind of natural hexadecimal size to them. A decimal data type value's storage size doesn't have a great correlation to its hex representation's length.

I still haven't looked at anyone's entries because I'm not done with mine, but you have three options:
• return some number of leading zeroes as you feel like it
• return a fixed size for different data types according to your own whim, perhaps based on some formula or value such as the data type's size.
• strip all leading zeroes, except in the case of when the final value is 0.

I also should have said in the op that the function must return an even number of hex characters. So 0 would return 00 and 256 would return 0100 (or 000100 or 00000100, and so on).

Feel free to edit your entries, if your edits make changes in these areas, or if you are using ideas from your own head and not being inspired by other people's work. (It's the honor system!)

-----------------------

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[AlexCuse]

Well, this doesn't meet the requirements (I can't figure out how to get C# to accept a decimal(32,0) except to use a float, and then I get accuracy issues), but I worked hard on it, so you guys get to read it. I tried using decimal data types and messing with the input (SQL side) but it was trimming off the first few characters of the hex string for larger numbers, and I don't have that much time to find the sweet spot in terms of accuracy and size allowed. So, I settled for float/bigint.

I am fairly confident that this will beat any t-sql implementation in terms of speed. I created a 10 million row numbers table, and it does the top or bottom 100k records in +/- 2.7 seconds, 1 million in about 16.5 (and my computer is rather slow, as we have found from other SQL teasers ;-) )

Besides, I couldn't post my 'fast' version, cause its' just like George's. Anyway, enjoy (and I'll remember spoiler tags this time)...

Spoiler

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;




public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString clrHex(long toConvert)
{
HexConverter hc = new HexConverter();

string retString = hc.toHex(toConvert);

return "00000000".Substring(0, 8 - (retString.Length % 8)) + retString;

hc = null;

}

public class HexConverter
{
public string toHex(long dIn)
{
string result;
long cMod;

cMod = dIn % 16; //modRep(dIn, 16);

if ((dIn - cMod) == 0)
{
result = HexChar(Convert.ToInt32(cMod));
}
else
{
result = toHex((dIn - cMod) / 16) + HexChar(Convert.ToInt32(cMod));
}


return result;
}

public static String HexChar(int cMod)
{
string retChars = "0123456789ABCDEF";
return retChars.Substring(cMod, 1);
}


}
};

I just ran some more tests (this time putting a PK on my numbers table)

here are results:
100k rows +/- 1.65 seconds
1 million rows +/- 15.9 seconds
10 million rows +/- (guess, you will be close ;-) )

Not too shabby

I might try and optimize this this weekend (I think putting it all into one class with only 2 methods could speed things up significantly, but I wanted to try it this way)

Alex




Ignorance of certain subjects is a great part of wisdom

 

[Jamfool]

Well if you can use c#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fnDecHex(SqlDecimal input)
    {
        char[] HexList = 
        new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' };
        string sReturn = "";
            for (ulong ulDec = ulong.Parse(input.ToString()); ulDec > 0; ulDec /= 16)
            {
                sReturn += HexList.GetValue((int)(ulDec % 16)).ToString();
            }
        return sReturn;
    }
};

 

[chrissie1]

A desimal in C# is decimal no need to specify the precision.

Christiaan Baes
Belgium

"My old site" - Me

 

[AlexCuse]

Jamfool -

SqlDecimal input That seems to be what I was missing. If I used a standard decimal as the input, it creates the function expecting an input of numeric(18,0).

I think you may be able to squeeze a little more range out of your function by working with decimals on the inside of your function rather than ulong's (I still don't think it'll be able to accept higher decimal(32,0) values though)

Anyway, have a purple thingy for showing me the error of my ways. Now I have a reason to install that visual studio demo CD I have on my desk at work :-D

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Jamfool]

cant accurately get the 38 digit number... so added the ability to change the base instead:

how you do spoiler tags?

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Text;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertToBase(SqlInt16 Inbasesystem, SqlDecimal Invalue)
    {
        int basesystem = (int)Inbasesystem;
        decimal value = (decimal)Invalue;

        string digits = "0123456789ABCDEF".Substring(0, basesystem);
        int remainder;

        StringBuilder sReturn = new StringBuilder();
        do
        {
            remainder = (int)(value % basesystem);
            sReturn.Insert(0, digits[remainder]);
            value = (value - remainder) / basesystem;
        } while (value != 0);

        return sReturn.ToString();
    }    
};

p.s sorry for wrong posting too

 

[AlexCuse]

spoiler tags are just like this:

[ignore]

Spoiler

what shouldn't we see

[/ignore]

Its' funny, I was just working on the same type of thing (passing in different base systems to the function). Bored great minds, and all that ;-)



Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Well, this seems to be dead or dying, but I will give one more shot to revive it. I added a function to bring the string back to a decimal, and also gave it the ability to do any base system up to 36 (even though I don't know if these actually would use the same characters or not). It does not convert to base 1, because that is just worthless!

I know it doesn't meet the challenge, but as the goal of these is to have some fun and maybe even learn something, maybe you guys would like to see:

Spoiler

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;




public class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString clrBase(long toConvert, long dBase)
{
if (dBase > 1)
{
BaseConverter bc = new BaseConverter();

string retString = bc.toBase(toConvert, dBase);

bc = null;

return "00000000".Substring(0, 8 - (retString.Length % 8)) + retString;
}
else
{
return "You Suck";
}

}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDecimal clrBaseDec(string toConvert, long dBase)
{
BaseConverter bc = new BaseConverter();

SqlDecimal result;

if (bc.basePos(toConvert, dBase) && dBase > 1 && dBase < 37)
{
result = bc.toDec(toConvert, dBase);
}
else
{
result = -1;
}

return result;


}

public class BaseConverter
{
public static readonly string retChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";

public string toBase(long dIn, long dBase)
{
string result;
long cMod;

cMod = dIn % dBase;

if ((dIn - cMod) == 0)
{
result = baseChar(Convert.ToInt32(cMod));
}
else
{
result = toBase((dIn - cMod) / dBase, dBase) + baseChar(Convert.ToInt32(cMod));
}


return result;
}

public long toDec(string toConvert, long dBase)
{
long result;

if (toConvert.Length == 1)
{
result = baseDec(toConvert, dBase);
}
else
{
result = toDec(toConvert.Substring(1, toConvert.Length - 1), dBase) + baseDec(toConvert, dBase);
}

return result;

}

private static string baseChar(int cMod)
{
return retChars.Substring(cMod, 1);
}

public static long baseDec(string cStr, long dBase)
{
return Convert.ToInt64(retChars.IndexOf(cStr.Substring(0,1)) * Math.Pow(dBase, cStr.Length - 1));
}

public Boolean basePos(string toConvert, long dBase)
{

int i = 0;
int iStop = toConvert.Length;

Boolean retVal = true; //default to true

while (i < iStop)
{
if (retChars.IndexOf(toConvert.Substring(0, 1)) < dBase && retChars.IndexOf(toConvert.Substring(0,1)) != -1)
{
//this is good
toConvert = toConvert.Substring(1, toConvert.Length - 1);
i++;
}
else
{
retVal = false;
break;
}
}

return retVal;
}
}
};

Perhaps the next teaser should be what to name these base systems, as the names here are rather odd

http://en.wikipedia.org/wiki/Numeral

;-)





Ignorance of certain subjects is a great part of wisdom