CODEWITHSUNDEEP
sqlsql-server
mhn
mhn

Reputation: 2750

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?

Upvotes: 2

Views: 6921

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 7

Related Questions