July 25, 2012 at 3:56 pm
Comments posted to this topic are about the item Calculate alphabetic sequence
August 8, 2012 at 2:27 pm
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...
September 6, 2012 at 4:05 pm
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;
May 10, 2016 at 6:46 am
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