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

 All Forums
 General SQL Server Forums
 Script Library
 Function to Convert Number to Base N

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-02 : 15:40:45
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 02:00:33
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
   

- Advertisement -