Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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  
 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.12 seconds. Powered By: Snitz Forums 2000