SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Function to Convert Number to Base N
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/02/2008 :  15:40:45  Show Profile  Reply with Quote
Function F_NUMERIC_TO_BASE_N converts an input numeric(32,0) value, @Number, to a string of digits in number base @Base, where @Base is between 2 and 36.

It is sometimes useful to convert a number to a non-base 10 string. For example, you might want to convert a number to base 36 to compress a long number into fewer characters; the number 2147483647 would become ZIK0ZJ in base 36.

The following script creates function F_NUMERIC_TO_BASE_N, and then runs code to test it with various values.

if objectproperty(object_id('dbo.F_NUMERIC_TO_BASE_N'),'IsScalarFunction') = 1	
	begin
	print 'Drop function dbo.F_NUMERIC_TO_BASE_N'
	drop function dbo.F_NUMERIC_TO_BASE_N
	end
go
create function dbo.F_NUMERIC_TO_BASE_N
	(
	@Number		numeric(32,0),
	@Base		int
	)
returns varchar(110)
as

/*
Function: F_NUMERIC_TO_BASE_N

	Function F_NUMERIC_TO_BASE_N converts a numeric(32,0) value, @Number,
	to a string of digits in number base @Base,
	where @Base is between 2 and 36.

	Output digits greater than 9 are represented by
	uppercase letters A through Z, where A = 10 through Z = 35.
 
	If input parameter @Number is negative, the output string
	will have a minus sign in the leftmost position.

	Any non-null numeric(32,0) value for parameter @Number is valid:
	-99999999999999999999999999999999 through
	 99999999999999999999999999999999.

	If input parameters @Number or @Base are null,
	or @Base is not between 2 and 36,
	then the function returns a null value.
*/

begin

declare @Work_Number	numeric(38,0)
declare @Modulus	int
declare @Digits		varchar(36)
declare @Output_String	varchar(110)

if @Number is null or @Base is null or @Base < 2 or @Base > 36 
	begin
	return null
	end

set @Digits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
set @Output_String = ''
set @Work_Number = @Number

while 1=1  
	begin

	Set @Modulus = convert(int,abs(@Work_Number-(round(@Work_Number/@Base,0,1)*@Base)))

	set @Output_String = substring(@Digits,@Modulus+1,1) + @Output_String

	set @Work_Number = round(@Work_Number/@Base,0,1)

	if @Work_Number = 0	break
	end -- end while

if @Number < 0 set @Output_String = '-'+@Output_String

return @Output_String

end
go

print  ''
print  'Run Test of dbo.F_NUMERIC_TO_BASE_N with various values'
print  ''

select
	a.number,
	b.base,
	dbo.F_NUMERIC_TO_BASE_N ( a.number, b.base )
from
	( -- Test values for numbers to be conveted to strings
	select number = convert(numeric(32,0),-99999999999999999999999999999999)	union all
	select number = convert(numeric(32,0), 99999999999999999999999999999999)	union all
	select number = convert(numeric(32,0), 523949341234234)			union all
	select number = convert(numeric(32,0), 74094949349234923)		union all
	select number = convert(numeric(32,0), null)				union all
	select number = convert(numeric(32,0), -1)				union all
	select number = convert(numeric(32,0), 0)				union all
	select number = convert(numeric(32,0), 36 )				union all
	select number = convert(numeric(32,0), 1679615 )			union all
	select number = convert(numeric(32,0), 60466175	)			union all
	select number = convert(numeric(32,0), 2147483647 )			union all
	select number = convert(numeric(32,0), -9223372036854775807 )		union all
	select number = convert(numeric(32,0), -9223372036854775808 )		union all

	select number = convert(numeric(32,0), 1)
	) a
	cross join
	( -- Test values for all possible valid bases, and some invalid bases
	select base = null		union all
	select base = 1		union all
	select base = 2		union all
	select base = 3		union all
	select base = 4		union all
	select base = 5		union all
	select base = 6		union all
	select base = 7		union all
	select base = 8		union all
	select base = 9		union all
	select base = 10	union all
	select base = 11	union all
	select base = 12	union all
	select base = 13	union all
	select base = 14	union all
	select base = 15	union all
	select base = 16	union all
	select base = 17	union all
	select base = 18	union all
	select base = 19	union all
	select base = 20	union all
	select base = 21	union all
	select base = 22	union all
	select base = 23	union all
	select base = 24	union all
	select base = 25	union all
	select base = 26	union all
	select base = 27	union all
	select base = 28	union all
	select base = 29	union all
	select base = 30	union all
	select base = 31	union all
	select base = 32	union all
	select base = 33	union all
	select base = 34	union all
	select base = 35	union all
	select base = 36	union all
	select base = 37
	) b
order by
	a.number,
	b.base






CODO ERGO SUM

Edited by - Michael Valentine Jones on 09/02/2008 18:11:30

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 09/03/2008 :  02:00:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86864



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000