What is the best way to find out how many records do not have a proper phone number in SQL Server?

Question

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them.

I've been looking around in SSIS for this. My inclination is to use a script component with a regex expression in some C# code, followed by a redirect. Still, I was wondering if there was a way in SSIS to do this without resorting to C#.

I haven't had a lot of luck finding a way to find out how many records do not have appropriate phone numbers.

Answer 1

It is one time task. Write a small application or just select script, use C#, VB.NET, T-SQL...and analyze all patterns manually. Maybe you will find out common patterns by operators who inputed this data. Add an algorithm for every pattern. Apply your patterns to real data in a database. Remove all "wrong" records.

Luck.

ADDED:

As option, you can use database of telephone numbers to check person2number validity if it's possible.

Answer 2

SSIS = SQL Server Integration Service - mostly a way of integrating data from many sources to many destinations. Something like an engine to take data from excel/csv/text .. what other file comes to mind.. and move it to a database. Or the other way around.

But to actually select and manipulate data you would still be able/be required to use T-SQL.

From what I know T-SQL doesn't have any regex component to help you, so you'd be required to use a .NET assembly to do that.

Answer 3

You could get a quick estimate via the following WHERE clause since phone numbers shouldn't have alpha characters... unless you allow phonetic numbers, ex. 1-800-ANT-FARM.

WHERE phonenumber LIKE '%[a-zA-Z]%'

You cannot do complex regex using LIKE, but you could get a close approximation.

My test:

WITH cte AS (
    SELECT id, phone
    FROM (
        VALUES
            (1, '1234567890'),
            (2, '4567890'),
            (3, '(123) 456-7890'),
            (4, '123-456-7890'),
            (5, '123.456.7890'),
            (6, 'Testing')
    )
    AS MyTable(id, phone)
)
SELECT *
FROM cte
WHERE phone LIKE '%[a-zA-Z]%'

Answer 4

It depends what is acceptable or not in a phone number

This gives you all values that are not 100% numeric by using NOT in the search pattern

WHERE phonenumber LIKE '%[^0-9]%'

But if you allow - or (000) then it's more complex: need sample data please

Answer 5

Try something like this:

WITH AreaCode (A) AS (
   SELECT '[0-9][0-9][0-9][-.]'
   UNION ALL SELECT '([0-9][0-9][0-9])-'
), Prefix (P) AS (
   SELECT '[0-9][0-9][0-9]-'
), Last4 (L) AS (
   SELECT '[0-9][0-9][0-9][0-9]'
), Ext1 (E1) AS (
   SELECT ' x'
   UNION ALL SELECT ' Ext.'
   UNION ALL SELECT ' ext'
), Ext2 (E2) AS (
   UNION ALL SELECT '[0-9][0-9]'
   UNION ALL SELECT '[0-9][0-9][0-9]'
   UNION ALL SELECT '[0-9][0-9][0-9][0-9]'
), Extension (E) AS (
  SELECT ''
  UNION ALL SELECT E1 + E2 FROM Ext1 CROSS JOIN Ext2
),
SELECT *
FROM
   YourTable Y
WHERE NOT EXISTS (
   SELECT *
   FROM
      AreaCode
      CROSS JOIN Prefix
      CROSS JOIN Last4
      CROSS JOIN Extension
   WHERE
      Y.PhoneNumber LIKE AreaCode + Prefix + Last4 + Extension
);

If you find patterns that are valid but not covered by the query, add them to the parts and pieces shown. If you find something that needs to be together in the two parts, then model it after the Extension CTE (which is either missing or a combination of Ext1 and Ext2). If you need to support international numbers, and they have different patterns (not matching the U.S. 3-3-4) then you'll need some analysis and proper correlating to make the right country codes match up with the right patterns. For example, I know that in certain parts of Brazil, this is a valid number: +55 85 1234-5678 (country code 55, area code two digits, then 4-4 pattern).

Another technique to help you analyze your data is this:

WITH Patterns (P) AS (
   SELECT
      Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
         PhoneNumber,
         '1', '0'), '2', '0'), '3', '0'), '4', '0'),
         '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'
      )
)
SELECT P, Count(*)
FROM Patterns
GROUP BY P;

This can help you understand what your data is like by ignoring the actual phone number differences between each row and paying attention only to the arrangement and count of digits. If there are a lot of alpha characters, try to start replacing valid patterns (such as "ext") with a value not found in the list, so you can collapse the rest of the spurious input into something that can be analyzed with a similar Replace() for each letter in the alphabet.