CODEWITHSUNDEEP
mysqlduplicates
user3286692
user3286692

Reputation: 383

Select all rows having duplicate phone numbers with different zone codes?

I have a table called contacts with following fields:

+----+-------+--------------+
| id | name  | phone_no     |
+----+-------+--------------+

Suppose, I have have following values in this table : 

+----+-------+--------------+
| id | name  | phone_no     |
+----+-------+--------------+
| 1  | Alex  | 9907661234   |--1, 2 are 
| 2  | Alex  | 09907661234  |--Same contacts but preceding with '0'
| 3  | John  | 9879612363   |--Same contacts but preceding with '91'
| 4  | John  | 919879612363 |-- 91 is (country code)
| 5  | Shawn | 9979867123   |
+----+-------+--------------+

I want to find number of duplicate contacts with duplicate numbers (here numbers preceded) with 0 and 91 are duplicates.

I want following output : 

+------------+-------------+
| phone_no   |     cn      |
+------------+-------------+
| 9879612363 |           2 |
| 9907661234 |           2 |
+------------+-------------+

Upvotes: 5

Views: 5129

Answers (7)

echo_Me
echo_Me

Reputation: 37233

i think you looking for this

select if(left(phone_no ,3) = 091 , substring( phone_no,4) ,
       if(left(phone_no ,1)= 0, substring( phone_no,2),
       if(left(phone_no ,2) = 91 , substring( phone_no,3), phone_no))) as phones, count(*) counts

from contacts
group by phones
having count(*)>1

DEMO HERE

Or esayily to select last 10 digits of your number phones

   select RIGHT(`phone_no`, 10) as phones , count(*) counts
   from contacts
   group by phones
   having count(*)>1

DEMO HERE

OUTPUT:

PHONES      COUNTS
9879612363  2
9907661234  2

Upvotes: 2

user1528581
user1528581

Reputation: 41

Please follow this answer. May be it will work.

select phone_no, count(*)
  from table_name
  group by phone_no
  having count(*) > 1

Upvotes: 2

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58271

Assuming you have phone numbers are 10 chars (as you shown in your question) and optionally prefixed by some codes. Then you can use RIGHT(str,len) function in MySQL that return the specified rightmost number of characters.

The query will be as follows(read comments): 

SELECT  RIGHT(`phone_no`, 10) as `mobile`,  -- selecting last 10 digits
        count(*) as `tatal_count`
FROM `table_name`
GROUP BY `mobile`  -- group by last ten digits
HAVING count(`mobile`) > 1;  -- if you want to select on duplicates

Working example:

Create table: 

CREATE TABLE IF NOT EXISTS `details` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `phone` varchar(64) DEFAULT NULL, 
  PRIMARY KEY (`id`)
)

Insert Query: 

INSERT INTO `details` VALUES 
("1", "Alex", "9907661234"),
("2", "Alex", "09907661234"),
("3", "John", "9879612363"),
("4", "John", "919879612363"),
("5", "Shawn", "9979867123");

[ANSWER] 

mysql> SELECT  RIGHT(`phone`, 10) as `mobile`, 
    ->         count(*) as `tatal_count`
    -> FROM `details`
    -> GROUP BY `mobile`
    -> ;
+------------+-------------+
| mobile     | tatal_count |
+------------+-------------+
| 9879612363 |           2 |
| 9907661234 |           2 |
| 9979867123 |           1 |
+------------+-------------+
3 rows in set (0.04 sec)

Suppose if you wants only numbers those duplicates (more than one) then youcan use HAVING clause in MySQL: 

mysql> SELECT  RIGHT(`phone`, 10) as `mobile`, 
    ->         count(*) as `tatal_count`
    -> FROM `details`
    -> GROUP BY `mobile`
    -> HAVING count(`mobile`) > 1;
+------------+-------------+
| mobile     | tatal_count |
+------------+-------------+
| 9879612363 |           2 |
| 9907661234 |           2 |
+------------+-------------+
2 rows in set (0.00 sec)

I am not check that codes are, and assuming you have valid mobile numbers in DB

Upvotes: 4

fthiella
fthiella

Reputation: 49069

If I understand your question correctly, this should be what you are looking for:

SELECT
  CASE
    WHEN phone_no LIKE '0%' THEN SUBSTR(phone_no FROM 2)
    WHEN phone_no LIKE '91%' THEN SUBSTR(phone_no FROM 3)
    ELSE phone_no
  END phone_no,
  COUNT(*) duplicates
FROM
  tablename
GROUP BY
  CASE
    WHEN phone_no LIKE '0%' THEN SUBSTR(phone_no FROM 2)
    WHEN phone_no LIKE '91%' THEN SUBSTR(phone_no FROM 3)
    ELSE phone_no
  END
HAVING
  COUNT(*)>1

Please see fiddle here.

Upvotes: 2

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

select 
 $phone_number as phone_number, count(phone_number) as cnt 
from my_table 
where phone_number like concat( '%', $phone_number )
group by concat( '%', $phone_number )

Upvotes: 2

Benz
Benz

Reputation: 2335

Well, I can't test it because I don't have an MySQL server here. But maybe you can use the following:

SELECT
    IF (
        phone_no REGEXP '^91' AND LENGTH(phone_no) = 12,
            SUBSTRING(phone_no, 3),
            IF (
                phone_no REGEXP '^0'  AND LENGTH(phone_no) = 11,
                    SUBSTRING(phone_no, 2),
                    phone_no
            )
    ) AS phoneNumber,
    COUNT(*) AS cnt
FROM
    `table` 
GROUP BY 
    phoneNumber

Replace the 'table' by your own table name.

What this query does, if checking if the phone number begins with 91 or 0. If so, it strips of the first 2 or 1 characters. After getting the correct number, we can use the 'phoneNumber' column for grouping.

EDIT: Also added 'LENGTH' in the query, just to be sure normal numbers are not being stripped.

Upvotes: 3

Moo-Juice
Moo-Juice

Reputation: 38800

Select the numbers in to a temporary table, stripping leading zeros, or where the number is over a specified length and the leading digits match a country code, cut them off.

You can then do a search on your temporary table for duplicates.

Upvotes: 2

Related Questions