By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 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
Share this Question
Share on Google+
4 Replies


MMcCarthy
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
  1. SELECT Left(PhoneNum,6), PhoneNum
  2. FROM TableName
  3. WHERE (((Left(PhoneNum,6)) In (SELECT Left(PhoneNum,6) FROM TableName As Tmp 
  4. GROUP BY Left(PhoneNum,6)
  5. HAVING Count(*)>1 )))
  6. ORDER BY Left(PhoneNum,6);
  7.  
Feb 11 '08 #2

Minion
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
  1. SELECT Left(PhoneNum,6), PhoneNum
  2. FROM TableName
  3. WHERE (((Left(PhoneNum,6)) In (SELECT Left(PhoneNum,6) FROM TableName As Tmp 
  4. GROUP BY Left(PhoneNum,6)
  5. HAVING Count(*)>1 )))
  6. ORDER BY Left(PhoneNum,6);
  7.  
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
  1. SELECT Left(PhoneNum,6), PhoneNum
  2. FROM TableName
  3. WHERE (((Left(PhoneNum,6)) In (SELECT Left(PhoneNum,6) FROM TableName As Tmp 
  4. GROUP BY Left(PhoneNum,6)
  5. HAVING Count(*)>1 )))
  6. ORDER BY Left(PhoneNum,6);
  7.  

Thank You, That worked perfectly. :)
Feb 11 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank You, That worked perfectly. :)
You're very welcome.

P.S. Good point Minion :)
Feb 11 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.