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?
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