IntToBase and back again... - SQL Server Forums

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

IntToBase and back again...

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-29 : 09:22:41

[code]
/****************************
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
[/code]

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."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-29 : 14:15:02

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."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources