When we installed our application, we received a data dump of patient records, this was pending our live interface feed from their patient admission system. When we got the dump, each patient has a unique code (medrecNo). When we got the dump, there were no leading zero's. Now that we have a live feed of data, it sends the medrecno with leading zero's. So after the live feed has been running for nearly two weeks, I have a bunch of duplicates, the only difference is that one has 1 or more leading zero's. I can't just delete the ones without a leading zero, because they may come into the facility tomorrow, before the live feed sends their data and they have to be available from a lookup. The medrecno is all numerics (but stored in a varchar). I was wondering if there is a way to right a query that would return me a record set that would be something like:

MedRecNo1  MedRecNo2   PatientID1   PatientID2 
01234567   1234567     1000         15001

Where the patientID is the PK on the patient table and the int value of the medrecnos matched


Thanks for any help

Kirk