CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Baub
Baub

Reputation: 751

Generate unique ID from string

I am trying to take a text string and create a unique numerical value from it and I am not having any luck.

For example, I have user names (first and last) and birthdate. I have tried taking these values and converting them to varbinary, which does give me a numerical value from the data, but it isn't unique. Out of ~700 records, I will get at least 100 numerical values that are duplicated but the text of first name, last name, and birthdate that was used to generate the number is different.

Here is some code I have been trying: 

SELECT CONVERT(VARCHAR(300), CONVERT(BIGINT,(CONVERT(VARBINARY, SE.FirstName) +  CONVERT(VARBINARY, SE.BirthDate) ))) FROM ELIGIBILITY SE

If I use that code and convert the following data, the result is 3530884780910457344. So the same number is generated from this unique data:

David       12/03/1952
Janice      12/23/1952
Michael     03/24/1952
Mark        12/23/1952

I am looking for some way, the simpler the better, to take these values and generate a unique numerical value from that data. And the reason why I need to use these values as input is because I am trying to avoid creating duplicates in the future as well as be able to predict the numerical value based on the formula. This is why NewID() won't work for me.

Upvotes: 1

Views: 8061

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

How about simply:

SELECT CHECKSUM(name, BirthDate) FROM dbo.ELIGIBILITY;

Of course, since there are still chances for collisions, maybe you should better define what you are actually trying to do. You've stated some reasons why e.g. NEWID() won't work but I still don't follow the the underlying purpose of this unique number.

Upvotes: 8

Related Questions