Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfConvertBase10NumberToAnyBase]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfConvertBase10NumberToAnyBase]
GO

/************************************************************************************************************************************/
-- This function takes a base-10 (i.e., Decimal) number (@iNumber parm) and converts it into a number of base @iNewBase.  
-- This SP is quite useful for generating much smaller (less total characters) alphanumeric-equivalents of a base-10 counterpart.
-- Can just as easily be used to convert base-10 to binary string, hexadecimal, etc.
-- Some Examples:
--	PRINT dbo.udfConvertBase10NumberToAnyBase(10, 2, 0, 0)		-- returns 1010 (BINARY)
--	PRINT dbo.udfConvertBase10NumberToAnyBase(16, 2, 0, 0)		-- returns 10000 (BINARY)
--	PRINT dbo.udfConvertBase10NumberToAnyBase(512, 36, 0, 0)	-- returns E8
--	PRINT dbo.udfConvertBase10NumberToAnyBase(512, 26, 0, 1)	-- returns TS
--	PRINT dbo.udfConvertBase10NumberToAnyBase(1000, 16, 0, 0)	-- returns 3E8 (HEX)
--	PRINT dbo.udfConvertBase10NumberToAnyBase(1000, 36, 0, 0)	-- returns RS
--	PRINT dbo.udfConvertBase10NumberToAnyBase(10000, 62, 0, 0)	-- returns 2bI
--	PRINT dbo.udfConvertBase10NumberToAnyBase(512, 52, 1, 1)	-- returns ERROR STRING
/************************************************************************************************************************************/
CREATE FUNCTION udfConvertBase10NumberToAnyBase
(	@iNumber			INT,
	@iNewBase			INT,
	@bitUppercaseOnly	BIT = 0,
	@bitNoNumbers		BIT = 0)  --1 to use only alphas
RETURNS VARCHAR(120)
AS
BEGIN
	DECLARE @vUppers	VARCHAR(26)
	DECLARE @vLowers	VARCHAR(26)
	DECLARE @vNumbers	VARCHAR(10)

	SELECT @vUppers = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
	SELECT @vLowers = 'abcdefghijklmnopqrstuvwxyz'
	SELECT @vNumbers= '0123456789'

	DECLARE @vCharactersInBase	VARCHAR(62) --Max possible of 10 digits, 26 upper, 26 lower
	SELECT @vCharactersInBase = @vUppers	--all options allow this

	IF @bitNoNumbers = 0
	BEGIN
		SELECT @vCharactersInBase = @vNumbers + @vCharactersInBase
	END

	IF @bitUppercaseOnly = 0
	BEGIN
		SELECT @vCharactersInBase = @vCharactersInBase + @vLowers
	END

	--If caller requests a "base" size > number of characters in conversion-characters-set, return error
	IF LEN(@vCharactersInBase) < @iNewBase
	BEGIN
		RETURN 'udfConvertBase10NumberToAnyBase - ERROR: Requested Base-size greater than available characters in @vCharactersInBase'
	END

	DECLARE @vNewNumber	VARCHAR(120)
	SELECT @vNewNumber = ''


	-- Algorithm for generating equivalant number in the new "base":
	-- 1) The orignial (base-10) number is continually divided by the new base until the product
	--    of the old number divided by the base is zero (meaning the number is finally smaller than the new base).
	-- 2) On each cycle (loop iteration), the remainder is added to the number, which is each digit of the new base.
	WHILE @iNumber <> 0
	BEGIN
		SELECT @vNewNumber = SUBSTRING(@vCharactersInBase, (@iNumber % @iNewBase) + 1, 1) + @vNewNumber
		SELECT @iNumber = @iNumber / @iNewBase
	END --While

	RETURN @vNewNumber

END --Procedure