mhn mhn - 5 months ago 12
SQL Question

Convert Alphanumeric value to a unique numeric value in SQL

I have a table column with values like below

V5H 3K3
V6L 4L4
V4E 5L2
V5H 3K3


I need to get a unique number against each of them so it would look something like

V5H 3K3 1111
V6L 4L4 2222
V4E 5L2 3333
V5H 3K3 1111


Is there a simple function in SQL Server that can be used to do this?

Answer
Select cast(HashBytes('MD5', 'V5H 3K3') as int)

Returns -381163718

For Example

Declare @Table table (SomeField varchar(25))
Insert into @Table values
('V5H 3K3'),
('V6L 4L4'),
('V4E 5L2'),
('V5H 3K3')

Select *,AsAnInt = abs(cast(HashBytes('MD5', SomeField) as int))
 From  @Table

Returns

SomeField   AsAnInt
V5H 3K3     381163718
V6L 4L4     245350301
V4E 5L2     1706996605
V5H 3K3     381163718