435,061 Members | 1,563 Online
Need help? Post your question and get tips & solutions from a community of 435,061 IT Pros & Developers. It's quick & easy.

# Compare first 6 digits of phone number

 P: 2 How do I write a query to compare the first 6 digits of a phone number and look for duplicates in the same table? I want to look at say 555-555-1234 and find all entries that have 555-555 stored as part of the phone number. Feb 11 '08 #1
4 Replies

 Expert Mod 10K+ P: 14,534 Try something like this, assuming PhoneNum is the name of your field and TableName as the name of your table. Expand|Select|Wrap|Line Numbers SELECT Left(PhoneNum,6), PhoneNum FROM TableName WHERE (((Left(PhoneNum,6)) In (SELECT Left(PhoneNum,6) FROM TableName As Tmp  GROUP BY Left(PhoneNum,6) HAVING Count(*)>1 ))) ORDER BY Left(PhoneNum,6);   Feb 11 '08 #2

 Expert 100+ P: 108 Try something like this, assuming PhoneNum is the name of your field and TableName as the name of your table. Expand|Select|Wrap|Line Numbers SELECT Left(PhoneNum,6), PhoneNum FROM TableName WHERE (((Left(PhoneNum,6)) In (SELECT Left(PhoneNum,6) FROM TableName As Tmp  GROUP BY Left(PhoneNum,6) HAVING Count(*)>1 ))) ORDER BY Left(PhoneNum,6);   Also, don't forget to make sure your table is not set up to save the delimeter or this will throw things off. - Minion - Feb 11 '08 #3

 P: 2 Try something like this, assuming PhoneNum is the name of your field and TableName as the name of your table. Expand|Select|Wrap|Line Numbers SELECT Left(PhoneNum,6), PhoneNum FROM TableName WHERE (((Left(PhoneNum,6)) In (SELECT Left(PhoneNum,6) FROM TableName As Tmp  GROUP BY Left(PhoneNum,6) HAVING Count(*)>1 ))) ORDER BY Left(PhoneNum,6);   Thank You, That worked perfectly. :) Feb 11 '08 #4

 Expert Mod 10K+ P: 14,534 Thank You, That worked perfectly. :) You're very welcome. P.S. Good point Minion :) Feb 11 '08 #5