Here is an example table and sample data:
CREATE TABLE `contacts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`phone` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_phone` (`phone`)
) ENGINE=InnoDB;
INSERT INTO `contacts` (`id`, `phone`)
VALUES
(1, NULL),
(2, '(123)'),
(3, '+222'),
(4, '+44 20 7895 1232'),
(5, '+448717923642'),
(6, ',123'),
(7, '1 (818) 555-1211'),
(8, '119999999999'),
(9, '123numbers'),
(10, '1555555555555'),
(11, '55555555'),
(12, '5555555555'),
(13, 'numbers'),
(14, 'numbers123');
Here is the query that gets my count:
SELECT database() as `database`, COUNT(*) as count
FROM
( SELECT database() as `database`,
phone,
TRIM(
TRIM(LEADING '1' FROM
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
phone,
'(',''),
')',''),
'-',''),
' ',''),
',','')
)
) as phone_changed,
CHAR_LENGTH(
TRIM(
TRIM(LEADING '1' FROM
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
phone,
'(',''),
')',''),
'-',''),
' ',''),
',','')
)
)
) as phone_changed_length
FROM contacts
WHERE phone NOT LIKE '+1%'
AND phone <> ''
AND phone IS NOT NULL
AND phone REGEXP '[0-9]+'
AND phone REGEXP '^[+]?[-(), 0-9]+$'
HAVING phone_changed_length <> 10
) as t
Trimming 1 off of the numbers may not be the perfect solution for getting rid of the 1 when someone puts it at the beginning of a US number but it worked fine for our situation. There are not any US area codes that start with 1 and it is mainly the US that uses the 1 for dialing a US phone number so we were not concerned that this count would be wrong. Example 2 and 6 in the above sample data will still get trimmed even though they are obviously not a valid US number and also not a international number and will still get counted.
The inner query is longer than it actually needs to be just to get this count, however, running the inner query by itself will allow you to see how the TRIM and REPLACE is stripping out some of the data and compare it to the original.
And to give credit where credit is due (needed to reference a few examples):
http://stackoverflow.com/questions/12652234/sql-query-to-remove-special-characters
http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql
https://techjourney.net/remove-or-trim-first-or-last-few-characters-in-mysql-database-with-sql/
http://stackoverflow.com/questions/8844880/mysql-regexp-removing-white-space-and-non-numeric-characters
http://dev.mysql.com/doc/refman/5.7/en/regexp.html
http://stackoverflow.com/questions/15005916/get-number-of-rows-returned-in-mysql-query
http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php
http://www.w3resource.com/mysql/string-functions/mysql-char_length-function.php
