Home > Utilities > Utility functions: fn_convert_to_base and fn_nums

Utility functions: fn_convert_to_base and fn_nums

I will often use code to illustrate my points in this blog. Because data generation is a big part of these examples, I will take the chance to introduce a few functions that I find useful for that. I will use these functions in my examples, so refer to the Utilities category on this blog to find the source. Most of these functions are adaption from other users of SQL Server and I will do my utmost to give credit where credit is due. If you feel you are the owner of the original idea – please send me an email so I can give you proper credit.

fn_convert_to_base is very useful for creating hex or binary representations of numbers. As we shall see, using the hex representation of a key can be good for illustrating some points. The version I use is based on the code by Leo Vindosola:

CREATE FUNCTION dbo.fn_convert_to_base  
    @value AS BIGINT,  
    @base AS INT = 16
    -- some variables  
    DECLARE @characters CHAR(36),  
            @result VARCHAR(MAX);  
    -- the encoding string and the default result  
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',  
           @result = '';  
    -- make sure it's something we can encode.  you can't have  
    -- base 1, but if we extended the length of our @character  
    -- string, we could have greater than base 36  
    IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;  
    -- until the value is completely converted, get the modulus  
    -- of the value and prepend it to the result string.  then  
    -- divide the value by the base and truncate the remainder  
    WHILE @value > 0  
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,  
               @value = @value / @base;  
    -- return our results  
    RETURN @result; 

Probably my all time favourite function is fn_nums that allows you to quickly generate a virtual table of integers. I use Itzik Ben Gan’s version:


    WITH  L0 AS(
        SELECT 1 AS c 
        UNION ALL 
        SELECT 1)
        ,  L1 AS(
            SELECT 1 AS c FROM L0 AS A, L0 AS B)
        ,  L2 AS (SELECT 1 AS c FROM L1 AS A, L1 AS B)
        ,  L3 AS (SELECT 1 AS c FROM L2 AS A, L2 AS B)
        ,  L4 AS (SELECT 1 AS c FROM L3 AS A, L3 AS B)
        ,  L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B)
        SELECT n FROM Nums   WHERE n <= @n;

For pasting source code into the blog, I use the Code Snippet Paster from Omar.

I am not too happy about the syntax formatting though, doesn’t recognize all keywords and comments. Hope someone reading can recommend a better plug-in for live writer.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s