Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you have sample data please? –  gbn Feb 18 '11 at 5:53
1  
Can you review the answers here please? –  gbn Jun 8 '11 at 6:00
    
How did things go? Did you pass this hurdle? –  ErikE Jul 6 '12 at 8:01

5 Answers 5

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.

share|improve this answer
    
I personally wouldn't remove 'wrong' numbers ... as it might be a pattern that you hadn't considered (eg, someone prefixing it with a '+') ... run it a few times in a debugging mode where it reports what it considers a bad value, and look through it before you have it make any modifications. –  Joe Feb 18 '11 at 13:47
    
@Joe agree: I used two columns "new" value and "old" one to backup data. It was just "strong" word :) –  garik Feb 18 '11 at 14:16

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.

share|improve this answer

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]%'
share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.