Convert Alphanumeric value to a unique numeric value in SQL

Question

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?

Does it _have_ to be `1111, 2222, ...` or do you just need _some_ unique number? e.h. could it be `1, 2, 3, 1`? — D Stanley, Jun 29 '16 at 19:26
You could treat it as a base 36 number and look for base 36 decoding functions. — Martin Smith, Jun 29 '16 at 19:28

John Cappelletti · Accepted Answer · 2016-06-29T19:35:56.933

7

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

edited Jun 29 '16 at 19:35

answered Jun 29 '16 at 19:31

John Cappelletti

79,615
7
44
66

It doesn't guarantee uniqueness of mapping though. There is a possibility of hash collisions. – Martin Smith Jun 29 '16 at 20:22
@MartinSmith Agreed, I should have qualified that. That said, I've yet to witness a collision – John Cappelletti Jun 29 '16 at 20:23
1

According to this answer it is indeed massively improbable with MD5 http://stackoverflow.com/a/288519/73226 – Martin Smith Jun 29 '16 at 20:28
@MartinSmith Great link! I can live with those odds. – John Cappelletti Jun 29 '16 at 20:31

Convert Alphanumeric value to a unique numeric value in SQL

1 Answers1