How to create a repeatable +ve integer from a nvarchar?

Question

Post reply

How to create a repeatable +ve integer from a nvarchar?

sarah.cook

SSC Veteran

Points: 268
More actions
June 6, 2013 at 3:44 pm

#294293

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!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

If a BIGINT is ok, you could consider this:

convert(bigint,convert(varbinary,'00sim384'))

BUT this will only work for strings up to a maximum of eight bytes in length so if you really have those values as nvarchar and are using unicode characters, you can't use this.

If you don't actually have any unicode in there and can convert to varchar first you can...

convert(bigint,convert(varbinary,convert(varchar,'00sim384')))

Why do you need to be able to repeat the conversion?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

Why not just grab the unique values and put then into a lookup table with an IDENTITY or BIGINT column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

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.

sarah.cook SSC Veteran Points: 268 More actions · Answer 4

Thanks for all the replies. We did end up converting to a BIGINT, and then just using a numeric conversion. (the values did remain very small, and since it's only a temporary solution we coudl work out that there was small risk of running out of room)

We had to have this repeatable since we wanted to do this in two different places and then match the values in the ETL.

We just came across this in another area and have used a ranking method to resolve it (very similar to the proposal to store them in a temporary id).

Very much appreciated