CODEWITHSUNDEEP
sqlsql-server
Alice
Alice

Reputation: 11

Find duplicates by partial string match

I have a table mytable that has an ID with leading zeros and a "duplicate" without leading zeros. i.e. I have 2 rows one is 456 and the other is 000456.The max data length for ones with leading zeros is 6 characters. I need to return the matches based of the fact that one has leading zeros, and the other does not.

I have tried:

SELECT constituent_id,  RIGHT('000000'+ISNULL(id,''),6) as missingbutpadded    
from mytable 
where id is not null and id not like '0%'

this returns the first column without leading zeros and the the same values in the second column with leading zeros up to 6 characters. How do I do a match to ones with leading zeros?

Here is the table right now: mytable 

|system_id| id   |first_name |last_name 
|1        |000456|James      | Smith    
|2        |456   |James      |Smith 
|3        |000555|Mary       |Carl  
|4        |555   |mary       |Carl

Expected_Results 

system_id |id    |matchedto |first_name |last_name
2         | 456  |000456    |James      |smith
4         | 555  |000555    |Mary       |Carl

Upvotes: 0

Views: 1542

Answers (1)

Dmitriy
Dmitriy

Reputation: 638

select table1.Id as MatchId, table2.Id as MatchedId
from yourtable table1
inner join yourtable table2 ON Convert(int,table1.Id)=Convert(int,table2.Id)
Where table1.id<>table2.id and table2.id like '0%'

That will do what you want.

Here it is with a test

create table #mytable(id varchar(50))
insert into #mytable(id) values ('45'), ('0056'),('0045')

select table1.Id as MatchId, table2.Id as MatchedId
from #mytable table1
inner join #mytable table2 ON Convert(int,table1.Id)=Convert(int,table2.Id)
Where table1.id<>table2.id and table2.id like '0%'

(3 row(s) affected) MatchId MatchedId

45 0045

(1 row(s) affected)

Upvotes: 1

Related Questions