Convert number to Hex or Base 36

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

SQL Server 2000 Forums

Transact-SQL (2000)

Convert number to Hex or Base 36

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-07-27 : 10:24:45

I need to create a "voucher" code that we can Email to customers.

A GUID would be great, but its awfully long

Simplest answer would probably be to have an INT, convert it to Base-36 (actually 0-9 and A-Z missing out O, I and maybe S, Z and B) as I think this would probably create large [secure - e.g. including checksum] numbers which were short to "transport"

Any pointers or SQL snippets would be helpful, thanks.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 10:44:44

Search this site: http://www.sqlservercentral.com/

For HEX or BASE64, quite few scripts.
(There is a cool topic if You search for BASE36 as well (not very base36 related though))

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 10:47:34

This guy's name... whoa
wonder if he has any transylvanian relatives.

http://www.utmag.com/wconnect/wc.dll?LevelExtreme~9,7,10,,105

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-27 : 10:55:32

so why doesn't this work for you?

select right(newid(), 8) as voucher

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-07-27 : 14:12:44

"select right(newid(), 8) as voucher"

I'm ignorant on GUIDS (except that I relatedly discovered that they might not be unique :-( )

Would RIGHT-8 be unique?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 14:18:51

>> I'm ignorant on GUIDS (except that I relatedly discovered that they might not be unique :-( )

In theory or practice ???

>> Would RIGHT-8 be unique?

I really don't think so.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-07-27 : 14:25:58

"In theory or practice ???"

Well .. they used to be based on the network card, but that was apparently regarded as an invasion of privacy ... so now its pot luck in that regard. So I think the risk is sufficiently tangible that we're going to trip over it sooner and probably not too much later.

I posted some link here in the last 6 months or so ... I'll see if I can dig it out.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 14:40:04

>> they used to be based on the network card

I didn't know they weren't anymore.
Time flies !

>> but that was apparently regarded as an invasion of privacy

Ever heard of surveillance cameras...
Must have been a EU regulation

>> I posted some link here in the last 6 months or so ... I'll see if I can dig it out

Use the search function.

I never liked the GUIDS anyway.
NEWSEQUENTIALID()
That's the new typingfriendly function, that generates readerfriendly keys,
that don't fuck up clustered indexes right !

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 03:27:25

GUIDs not guaranteed to be unique:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44507
and from there to
http://www.informit.com/articles/article.asp?p=25862&seqNum=5

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 05:36:15

just an observation...

select right(newid(), 8)
can return 2251875390625 possible combinations = 35^8.

lets say for argument sake that 1/3 of that will be unique.
that's still 750625130208 possible combinations.

i don't see what base-36 enconding can do better here... can you maybe explain?
i'd like to know...

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-28 : 06:51:25

quote:
Originally posted by spirit1

just an observation...

select right(newid(), 8)
can return 2251875390625 possible combinations = 35^8.

lets say for argument sake that 1/3 of that will be unique.
that's still 750625130208 possible combinations.

i don't see what base-36 enconding can do better here... can you maybe explain?
i'd like to know...

Go with the flow & have fun! Else fight the flow

Assume that everytime You select a number it's a random on from a bag. (in this case 35^8 # in the bag).
What is the possibility that You reselect one that You have already selected ?

I used to be able to do this, Corey !!!, where are You ?

Here is a recursive snippet that will calculate how many numbers
You can select from a bag before the probability of getting a duplicate > 50 %.

The bag just contains 1,000,000,000 #, but after only 37233 picks,
my chance of duplicate is 50%.

create table prob(x bigint, p float(53), n bigint)

insert prob(x,p,n) values(0,0,1000000000) --<----------------  1000,000,000 possibilities

select n as [possibilities] from prob

print '';print 'number x of numbers selected such that the probability of selecting duplicate = 50%'
while( (select p from prob) < .50 )
update prob set
	x = p1.x + 1
	,p = ( (p1.x+1.0)/n + p1.p ) - ( (p1.x+1.0)/n * p1.p )
from prob p1
select * from prob

print '';print 'number x of numbers selected such that the probability of selecting duplicate = 60%'
while( (select p from prob) < .60 )
update prob set
	x = p1.x + 1
	,p = ( (p1.x+1.0)/n + p1.p ) - ( (p1.x+1.0)/n * p1.p )
from prob p1
select * from prob
go

drop table prob
go

With 35^8 (100% random) possibilities, I have a 1% chance of getting a duplicate after only : 212754 picks !!!

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 07:03:38

I've actually got two separate but related problems.

1) We want to allocate one-time passwords - they can obviously have duplicates, but preferably not all be the same!

2) I want a one-time Voucher Code which MUST be unique, and not guessable.

Both should have a relatively short code (easy to type in) that offers plenty of combinations.

I was trying to avoid having a loop to convert to some sort of BASEn

What I've come up with so far is using [least significant] digits from RAND(@MyValue) in combination with @MyValue itself. The stuff from RAND can be appended to create a "checksum" of sorts. Then I am converting the number to BASE36 to reduce its length.

I could use some characters from NewID() to randomise the value, but this would not be reproducible (not particularly a problem with Vouchers, but it is handy if the hashing algorithm is repeatable)

My BASE36 function does not include any vowels (to reduce chance of swear words), nor S,Z, nor 0, 1, 2, 5 so that people keying them in will not confuse numbers and letters - so its actually BASE24.

For the password I am combining @MyValue with a multiple of Hour, Minute and Millisecond to get a further randomising effect - e.g. for a given UserID/@MyValue the One Time Password tomorrow will be different to the one you were given yesterday.

Thanks for the links Rocky - they set me on the path to my solution.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 07:04:57

yeah... my probabilty days are also counted

but this still looks like a lot to me... right-8 seems to do the job ok...

EDIT: haven't seen Kristen's reply before posting mine...
ok i see your point

thanx for the info.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-28 : 07:10:14

Thanks for the links, Kristen.
Ineteresting read.
IMO what speaks against guids is (generally):
- nonsequntiality (can be solved)
- 16 bytes (can't be solved, but not a huge deal)
- unreadable / unwritable / unpresentable (good design, will hide it from all but the DBA)
- maybe not so "Globally" unique (I might be able to handle the error when it comes)

PROS
- works well in distributed systems
- can be generated on the client
- can be updated (I have yet to see that being an issue though)

I almost never use them.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 07:21:24

I reckon you've got it covered Rocky.

Couple of observations:

- maybe not so "Globally" unique (I might be able to handle the error when it comes)

None of our legacy code has any handling for a GUID not being unique - we assume they were [and didn't know that MAC address was no longer part of the deal].

The new GUID type in SQL2005 will help I assume.

We probably ought to invent our own server-unique numbers. They would be smaller than 16 bytes I expect. Even an IDENTITY plus a second column [which could probably be smallint] for "server-specific ID" would probably do the trick

can be generated on the client

I reckon this widens the possibility of Duplicates

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-28 : 09:04:26

my contribution.... sorry I'm late


Create Function dbo.intToBase
(
	@n bigint,	
	@base varchar(1000) -- you could hardcode this instead
)
Returns varchar(1000) As
Begin
--	Set @base = '0123456789bcdfghjkmnopqrstuvwxyzBCDFGHJKLMNPQRTVWXYZ'

	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

Declare @n bigint,
	@base varchar(1000)

Set @n = 654321656655495462
Set @base = '0123456789bcdfghjkmnopqrstuvwxyzBCDFGHJKLMNPQRTVWXYZ'


Select @n, @base, dbo.intToBase(@n,@base)

Go
Drop Function dbo.intToBase

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-28 : 09:25:56

SELECT dbo.intToBase(591861,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')

You should post that in the script library.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-28 : 09:41:04

Found this in the basement.
Rudimentarily tested ( like 12 times ), and room for improvement

CREATE   FUNCTION dbo.fn_basetodec
   (@val AS VARCHAR(63), @base AS int)
  RETURNS BIGINT
AS
BEGIN
  RETURN
    (SELECT SUM(
       (CHARINDEX(
          SUBSTRING(@val, LEN(@val) - n + 1, 1),
          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)
       * POWER(CAST(@base AS BIGINT), n-1))
     FROM
	(
		SELECT n = 1+number FROM master.dbo.spt_values WHERE type = 'P' AND number < 64
	) Nums
     WHERE n <= LEN(@val))
END

Edit:
SELECT dbo.fn_basetodec('WHAT NUMBER',36)

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 10:26:35

Now that's what we need - a Tally Table Solution. I now remember I used POWER() similarly to grab the BITs out of something, yonks ago.

Darn it, I'll have to revisit my solution now!

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-28 : 11:16:58

you don't need a tally table for that... its simpler as a simple loop

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

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 11:29:19

Simpler or faster?

I'd be happy [in this case] with Complex & Faster, rather than Simple & Slower

Kristen

Next Page