Calculate alphabetic sequence

  • Comments posted to this topic are about the item Calculate alphabetic sequence

  • The article is lacking in details, examples and explanations...

    Of particular interest would be comparing a CLR function's performance to the stored procedure, below, and to a SQL UDF (:sick:)...

    I thought I would add the following info which can be expanded by other SSC members.

    The following procedure will convert a bigint value to a customizable Bijective (http://en.wikipedia.org/wiki/Bijective_numeration) value:

    CREATE PROCEDURE

    base10ToBijectiveBaseX

    @input BIGINT

    ,@digits NVARCHAR( 30 ) = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    ,@output NVARCHAR( 100 ) = N'' OUTPUT

    AS

    BEGIN

    DECLARE

    @length BIGINT

    ,@remainder BIGINT

    ;

    SELECT

    @length = LEN( @digits )

    ;

    WHILE @input > 0

    BEGIN

    SELECT

    @remainder = @input % @length

    ;

    IF @remainder = 0

    SELECT

    @remainder = @length

    ;

    SELECT

    @output = SUBSTRING(

    @digits

    ,@remainder

    ,1

    ) + @output

    ,@input = ( @input - @remainder ) / @length

    ;

    END

    -- Include the following lines if you want to output the value as a table value

    SELECT

    @output

    ;

    END

    Execute the proceure using code similar to this:

    DECLARE

    @converted NVARCHAR( 100 )

    ,@myValue BIGINT

    ;

    SELECT

    @myValue = 220222

    ;

    EXEC base10ToBijectiveBaseX

    @input = @myValue

    ,@output = @converted OUTPUT

    ;

    SELECT

    @converted AS convertedValue

    ;

    Or...

    DECLARE

    @converted NVARCHAR( 100 )

    ,@myValue BIGINT

    ;

    SELECT

    @myValue = 220222

    ;

    EXEC base10ToBijectiveBaseX

    @input = @myValue

    ,@digits = N'AaBbCcXxYyZz'

    ,@output = @converted OUTPUT

    ;

    SELECT

    @converted AS convertedValue

    ;

    I wil omit adding the reverse (BijectiveToBase10) code in hopes of encouraging someone else to participate...

  • Ouch this hurt my brain and took me an hour...but it is slick!

    This converts from any base to any base, like hex to decimal or octal.

    /*

    William Talada

    Define baseX and baseY

    Pass in a baseX value and it gets converted to baseY

    Currently set to convert hex to binary but can handle any imaginary base.

    */

    declare

    @ina varchar(50),

    @baseX varchar(30),

    @baseY varchar(30)

    set @ina = 'FF';

    set @baseX = '0123456789ABCDEF';-- must always start with zero even if never used (column placeholder for numbers like 20, 130, etc).

    set @baseY = '01';-- must always start with zero even if never used (column placeholder).

    declare

    @i int,

    @in10 int,

    @div10 int,

    @outB varchar(50),

    @RadixA int,

    @RadixB int;

    set @outB = '';

    set @RadixA = len(@baseX);

    set @RadixB = len(@baseY);

    -- first convert @ina to @in10, base10

    set @ina = reverse(@inA);

    set @i = 1;

    set @in10 = 0;

    while @i <= len(@inA)

    begin

    set @in10 = @in10 + ((charindex(substring(@inA, @i, 1), @baseX) - 1) * power(@radixA, @i-1))

    set @i = @i + 1;

    end

    select @in10;

    -- now convert to new base

    set @i = 1;

    while (@in10 / power(@radixB, @i)) > 0

    set @i = @i + 1;-- calculate number of columns needed for destination radix.

    set @i = @i - 1;-- overshot so back up.

    While @i >= 0

    begin

    set @div10 = @in10 / power(@radixB, @i);-- get magnitude of left most digit in destination radix.

    set @in10 = @in10 - (@div10 * power(@radixB, @i));-- set remainder

    set @outB = @outB + substring(@baseY, @div10+1, 1);-- build output string

    set @i = @i - 1-- calc next lower magnitude

    end

    select @outB;

  • Thanks for the script.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply