Convert alphanumeric ID into integer number

Question

I have clients which have the same client ID but all the IDs have different formats ie. some are all integers and some hare alphanumeric. I want to convert the entire ID into a unique integer for each patient.

I have 3 methods that I have found that are close but produce duplications one way or another.

Here are the 3 which use alphanumID as the original client ID and numericID as the converted integer:

-- Method 1 has many duplications of numericID throughout the table

DECLARE @EUGENIO GONZALEZ TABLE(alphanumID VARCHAR(11));
INSERT INTO @EUGENIO GONZALEZ VALUES
('0000005692'),('w000005692'),('173031'),('14160525'),('2229301'),('2229301'), ('000H304979'),('w0000005692'),('0000000017'),('0000000002'),('2229301'),('2229301');
SELECT*, CAST(CAST(alphanumID AS VARBINARY(4)) AS BIGINT) as numericID
FROM @EUGENIO GONZALEZ ;

-- Method 2 produces a duplicate numericID for the alphanumIDs '0000005692' and 'w000005692'

DECLARE @dummy2 TABLE(alphanumID VARCHAR(11));
INSERT INTO @dummy2 VALUES
('0000005692'),('w000005692'),('173031'),('14160525'),('2229301'),('2229301'), ('000H304979'),('w0000005692'),('0000000017'),('0000000002'),('2229301'),('2229301');
SELECT*, CAST(CAST(alphanumID AS VARBINARY(8000)) AS BIGINT) as numericID
FROM @dummy2;

-- Method 3 is a known issue where Hash with MD5 will produce collisions although this is not apparent in such a small sample, it is in 1.8 million records

DECLARE @dummy3 TABLE(alphanumID VARCHAR(11));
INSERT INTO @dummy3 VALUES
('0000005692'),('w000005692'),('173031'),('14160525'),('2229301'),('2229301'), ('000H304979'),('w0000005692'),('0000000017'),('0000000002'),('2229301'),('2229301');
SELECT*, abs(cast(HashBytes('MD5', alphanumID) as int)) as numericID
FROM @dummy3;

I can possibly live with these duplications, but I'd rather not and I'm hoping there is an easy solution.

Answer 1

Hi @Paula Morrissey (DHHS) ，

Please refer to：

 DECLARE @dummy TABLE(alphanumID VARCHAR(11));  
 INSERT INTO @dummy VALUES  
 ('0000005692'),('w000005692'),('173031'),('14160525'),('2229301'),('2229301'), ('000H304979'),('w0000005692'),('0000000017'),('0000000002'),('2229301'),('2229301');  
      
 ;with cte1  
 as (SELECT*, CAST(CAST(alphanumID AS VARBINARY(4)) AS BIGINT) as numericID  
 FROM @dummy)  
 ,cte2 as(SELECT*,rank() over(order by alphanumID)rn from cte1)  
      
 select alphanumID,cast(numericID as varchar)+cast(rn as varchar) numericID from cte2  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Consider the next approach too:

DECLARE @dummy TABLE(alphanumID VARCHAR(11))

INSERT INTO @dummy VALUES
('0000005692'),('w000005692'),('173031'),('14160525'),('2229301'),('2229301'), 
('000H304979'),('w0000005692'),('0000000017'),('0000000002'),
('2229301'),('2229301'),
('a'),
('b'),
('b'),
('a'),
('a'),
('c')

;
with N as
(
    select alphanumID 
    from @dummy
    where try_cast( alphanumID as int) is not null
),
A1 as
(
    select alphanumID 
    from @dummy
    where try_cast( alphanumID as int) is null
),
A2 as
(
    select *, dense_rank() over (order by alphanumID) as n
    from A1
)
select alphanumID, cast(alphanumID as int) as numericID
from N
union all
select alphanumID, n + (select MAX(alphanumID) from N) as numericID
from A2
order by alphanumID, numericID

/*

alphanumID  numericID
----------- --------------------
0000000002  2
0000000017  17
0000005692  5692
000H304979  2229302
14160525    14160525
173031      173031
2229301     2229301
2229301     2229301
2229301     2229301
2229301     2229301
a           2229303
a           2229303
a           2229303
b           2229304
b           2229304
c           2229305
w0000005692 2229306
w000005692  2229307

*/

Answer 3

According to your description, I revised the answer, please check if it works.

Echo