sarah.cook (6/6/2013)
Hi,I need to create an id from a nvarchar value that is repeatable, sort of like a checksum or a hash, except that it needs to be unique and positive. Does anyone know how I might do this? I've been scouring the net with no results yet...
To be clear I need to have some function that will turn '00sim384' (and about 3mm other alphanumeric values) into an integer. It also needs to produce the same integer value each and every time.
Before you ask Why? The reason is that we aren't yet storing unique ids for these values yet, so I'm trying to similate a unique integer for each of the values until we can load these into a table and just use the index. Sux to have to do this, but I've got all this generic processing that relies on having this integer id..
Thanks!
The max value of an INT is 2,147,483,647.
Let's say that your varchar column can contain only numbers and letters (upper and lower case). That's 10 + 26 + 26 = 62 options per character position.
I did some quick maths:
String Length Number of combinations
1 62
2 3,844
3 238,328
4 14,776,336
5 916,132,832
6 56,800,235,584
7 3,521,614,606,208
8 218,340,105,584,896
9 13,537,086,546,263,600
10 839,299,365,868,340,000
I think you can see the problem here - you run out of room for all the different possible combinations if your maximum string length is > 5.
And if you use a BIGINT (max 9,223,372,036,854,775,807) you can squeeze in only another 5 characters or so.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.