Alice Alice - 7 months ago 16
SQL Question

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

Answer
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)

Comments