Fuzzy Data Matching with SQL

Chapter 12. Tying It All Together

Now we’re going to look at everything we’ve covered in one place. This chapter is going to take the datasets we’ve already been working with, and the examples from each chapter, and see what we can glean out of them to answer the following questions:

• How many existing customers are in the import data?

• Conversely, how many new prospects are in the import data?

Along the way we will have to do a lot of data cleanup and data normalization to answer those questions. You will re-meet the Snedleys, of course.

Approach

A lot of this could look something like functional programming. Remember this?

SELECT
  CustomField3,
  CASE
    /*
       If NULL no worries.
    */
    WHEN CustomField3 IS NULL THEN 'No Email Found'
    /*
       If no @ in string, no email address found.
    */
    WHEN CHARINDEX('@', CustomField3) = 0 THEN 'No Email Found'
    /*
       If @ and no comma, then only email address in string.
    */
    WHEN CHARINDEX('@', CustomField3) > 0
      AND CHARINDEX(',', CustomField3) = 0
      THEN CustomField3
    /*
       If the email is first on the left, grab it.
    */
    WHEN CHARINDEX('@', CustomField3) > 0
      AND CHARINDEX(',', CustomField3) > CHARINDEX('@', CustomField3)
      THEN LEFT(CustomField3, CHARINDEX(',', CustomField3) - 1)
    /*
      If email is in the middle, then hold on!
    */
    WHEN CHARINDEX(',', CustomField3) > 0
     AND CHARINDEX('@', RIGHT(CustomField3, LEN(CustomField3) - 
                              CHARINDEX(',', CustomField3))) >
          CHARINDEX(',', CustomField3)
      AND CHARINDEX('@', RIGHT(CustomField3, LEN(CustomField3) -
                               CHARINDEX(',', CustomField3 ...