Unique HASHID with 20 characters limit using SQL Server-CodePudding

A code has a 20 character limit but it needs to be unique. To make it unique there are 3 parts combinations of these values will exceed the 20 characters limits, more like 40 – 50 size. So the idea is to generate some type of unique ID based on that string, but does not exceed 20 characters.

I was exploring the possible use of SQL Server Hashbytes function which put everything into hash and make it smaller (examples below).

However, all of the old 16 bit/32 bit hash function has been retired by Microsoft, and the newer ones which a lot more accurate will have something around 30 – 40 characters. Which defeats the purpose.

Any ideas?

CodePudding user response：

Just a thought...

On option is to convert the hashbytes() to a bigint.

Example

Declare @Table table (Col1 varchar(25),Col2 varchar(25),Col3 int)
Insert into @Table values
('V5H 3K3','AAA',25),
('V6L 4L4','VVV',16),
('V4E 5L2','CCC',25),
('V5H 3K3','AAA',27)

Select *
      ,UniID = left(abs(convert(bigint,HashBytes('MD5', concat(col1,col2,col3)))),20)
 From  @Table

Results

Col1        Col2    Col3    UniID
V5H 3K3     AAA     25      8561734070678522554
V6L 4L4     VVV     16      2225152452607129855
V4E 5L2     CCC     25      5842957356638096832
V5H 3K3     AAA     27      1688308399631225138