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...