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
 IntToBase and back again...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/29/2005 :  09:22:41  Show Profile  Visit Seventhnight's Homepage  Reply with Quote

/****************************
This function converts an int 
using a supplied base
****************************/
Create Function dbo.intToBase
(
	@n bigint,	
	@base varchar(1000)
)
Returns varchar(1000) As
Begin
	Declare @baseNum int
	Set @baseNum = len(@base)

	Declare @code varchar(1000)

	while (@n>0)
		Select @code=substring(@base,@n%@baseNum+1,1)+isnull(@code,''), @n=@n/@baseNum

	Return @code
End
Go
/****************************
This function converts a base- 
converted value to the original 
int using a supplied base
****************************/
Create Function dbo.baseToInt
(
	@code varchar(1000),	
	@base varchar(1000)
)
Returns bigint As
Begin
	Declare @baseNum int
	Set @baseNum = len(@base)

	Declare @n bigint
	Select @n = charindex(left(@code,1) Collate Latin1_General_CS_AS_KS_WS,@base Collate Latin1_General_CS_AS_KS_WS)-1, @code=right(@code,len(@code)-1)

	While (len(@code)>0)
		Select @n=(@n*@baseNum)+charindex(left(@code,1) Collate Latin1_General_CS_AS_KS_WS,@base Collate Latin1_General_CS_AS_KS_WS)-1, @code=right(@code,len(@code)-1)

	Return @n
End
Go

Declare @n bigint,
	@n2 bigint,
	@base varchar(1000),
	@code varchar(100)

-- This uses the full datetime (yyyymmddhhnnmmmm) but in reverse to get greater variation in the resulting base
Set @n = convert(bigint,reverse(dbo.getCharacters(convert(varchar,getdate(),121),'0-9')))

Set @base = '0123456789bcdfghjkmnopqrstuvwxyzBCDFGHJKLMNPQRTVWXYZ'
Set @code = dbo.intToBase(@n,@base)
Set @n2 = dbo.baseToInt(@code,@base)

Select base=@base, n=@n, baseFromN=@code, nFromBase=@n2
Go

Drop Function dbo.intToBase
Drop Function dbo.baseToInt


Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Edited by - Seventhnight on 07/29/2005 14:11:52

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/29/2005 :  14:15:02  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
By the way, I'm referencing a function called getCharacters in the demonstration above... so here it is:


/*********************************
Removes any characters from 
@myString that do not meet the 
provided criteria.
*********************************/
CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN
 
	While @myString like '%[^' + @validChars + ']%'
		Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

	Return @myString
END
Go


Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
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.19 seconds. Powered By: Snitz Forums 2000