Compare first 6 digits of phone number

[wjlugaila]

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.

[MMcCarthy]

Try something like this, assuming PhoneNum is the name of your field and TableName as the name of your table.
[code=sql]
SELECT Left(PhoneNum,6 ), PhoneNum
FROM TableName
WHERE (((Left(PhoneNu m,6)) In (SELECT Left(PhoneNum,6 ) FROM TableName As Tmp
GROUP BY Left(PhoneNum,6 )
HAVING Count(*)>1 )))
ORDER BY Left(PhoneNum,6 );
[/code]

[Minion]

Try something like this, assuming PhoneNum is the name of your field and TableName as the name of your table.
[code=sql]
SELECT Left(PhoneNum,6 ), PhoneNum
FROM TableName
WHERE (((Left(PhoneNu m,6)) In (SELECT Left(PhoneNum,6 ) FROM TableName As Tmp
GROUP BY Left(PhoneNum,6 )
HAVING Count(*)>1 )))
ORDER BY Left(PhoneNum,6 );
[/code]

Also, don't forget to make sure your table is not set up to save the delimeter or this will throw things off.

- Minion -

[wjlugaila]

Try something like this, assuming PhoneNum is the name of your field and TableName as the name of your table.
[code=sql]
SELECT Left(PhoneNum,6 ), PhoneNum
FROM TableName
WHERE (((Left(PhoneNu m,6)) In (SELECT Left(PhoneNum,6 ) FROM TableName As Tmp
GROUP BY Left(PhoneNum,6 )
HAVING Count(*)>1 )))
ORDER BY Left(PhoneNum,6 );
[/code]

Thank You, That worked perfectly. :)

[MMcCarthy]

Thank You, That worked perfectly. :)

You're very welcome.

P.S. Good point Minion :)