Fuzzy Data Matching with SQL

Fuzzy Data Matching with SQL

by Jim Lehmer
Released October 2023
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098152277

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

If you were handed two different but related sets of data, what tools would you use to find the matches? What if all you had was SQL SELECT access to a database? In this practical book, author Jim Lehmer provides best practices, techniques, and tricks to help you import, clean, match, score, and think about heterogeneous data using SQL.

DBAs, programmers, business analysts, and data scientists will learn how to identify and remove duplicates, parse strings, extract data from XML and JSON, generate SQL using SQL, regularize data and prepare datasets, and apply data quality and ETL approaches for finding the similarities and differences between various expressions of the same data.

Full of real-world techniques, the examples in the book contain working code. You'll learn how to:

  • Identity and remove duplicates in two different datasets using SQL
  • Regularize data and achieve data quality using SQL
  • Extract data from XML and JSON
  • Generate SQL using SQL to increase your productivity
  • Prepare datasets for import, merging, and better analysis using SQL
  • Report results using SQL
  • Apply data quality and ETL approaches to finding similarities and differences between various expressions of the same data

Table of contents

  1. Preface
    1. What Problems Are We Trying to Solve?
    2. What Will We Cover?
      1. Part I: Review
      2. Part II: Various Data Problems
      3. Part III: Bringing It Together
      4. Appendix
    3. Who Is This Book For?
    4. Why SQL?
    5. Warning! Opinions Ahead!
    6. Typographical Conventions Used in This Book
    7. Additional Information on the Book’s Conventions
    8. The Data “Model”
      1. Environment Layout
      2. Customer Table
      3. “Normalized” View
      4. Meet the Snedleys
    9. Using Code Examples
    10. O’Reilly Online Learning
    11. How to Contact Us
    12. Acknowledgments
  2. I. Review
  3. 1. A SELECT Review
    1. Simple SELECT Statements
    2. Common Table Expressions
    3. In CASE of Emergency
    4. Joins
      1. A Diversion into NULL Values
      2. OUTER JOINs
      3. Finding the Most Current Value
    5. Final Thoughts on SELECT
  4. 2. Function Junction
    1. Aggregate Functions
      1. MAX
      2. MIN
      3. COUNT
      4. SUM
      5. AVG
    2. Conversion Functions
      1. CAST and CONVERT
      2. COALESCE
      3. TRY_CONVERT
    3. Cryptographic Functions: HASHBYTES
    4. Date and Time Functions
      1. GETDATE
      2. DATEADD
      3. DATEDIFF
      4. DATEPART
      5. ISDATE
    5. Logical Functions: IIF
    6. String Functions
      1. CHARINDEX and PATINDEX
      2. LEN
      3. LEFT, RIGHT, and SUBSTRING
      4. LTRIM, RTRIM, and TRIM
      5. LOWER and UPPER
      6. REPLACE and TRANSLATE
      7. REVERSE
      8. STRING_AGG
    7. System Functions
      1. ISNULL
      2. ISNUMERIC
    8. Final Thoughts on Functions
  5. II. Various Data Problems
  6. 3. Names, Names, Names
    1. What’s in a Name?
    2. Last Names
      1. Punctuation
      2. Suffixes
    3. First Names
    4. Middle Name
    5. Nicknames
    6. Company Name
    7. Full Name
    8. “Person-Like Entities”
    9. Final Thoughts on Names
  7. 4. Location, Location, Location
    1. What Makes an Address?
    2. Street Address
      1. Box, Suite, Lot, or Apartment Number
      2. Don’t Overdo It!
    3. City
    4. County
    5. State or State Abbreviation
    6. ZIP or Postal Code
    7. Country
    8. Final Thoughts on Locations
  8. 5. Dates, Dates, Dates
    1. Time Is Relative
    2. Final Thoughts on Dates
  9. 6. Email
    1. What Makes a Valid Email Address?
    2. Final Thoughts on Email
  10. 7. Phone Numbers
    1. What Makes a “Phone Number”?
    2. One Final Note on Tax IDs
    3. Final Thoughts on Phone Numbers (and Tax IDs)
  11. 8. Bad Characters
    1. Data Representations
    2. Invisible Whitespace
    3. COLLATE
    4. Cleaning Up the Input Data
    5. Final Thoughts on Bad Characters
  12. 9. Orthogonal Data
    1. A Common Problem, A Common Solution, A New Common Problem
    2. Lather, Rinse, Repeat
    3. Final Thoughts on Orthogonal Data
  13. III. Bringing It Together
  14. 10. The Big Score
    1. What Will We Want?
    2. Tuning Scores
    3. Eliminating Duplicates
      1. Duplicate Data
      2. Duplicated Data
    4. Final Thoughts on Scoring
  15. 11. Data Quality, or GIGO
    1. Sneaking Data Quality In
    2. Impossible Data
      1. Simply Wrong
      2. Semantically Wrong
    3. ETL Your Way to Success
    4. Final Thoughts on Data Quality
  16. 12. Tying It All Together
    1. Approach
    2. What’s the Score?
    3. First Pass: Naive Matching
    4. Second Pass: Normalizing Relations
      1. Impossible Data
      2. Now Let’s Normalize
    5. Third Pass: Score!
    6. What About Tuning?
    7. Final Thoughts on Practical Matters
  17. 13. Code Is Data, Too!
    1. Working with XML Data
    2. Working with JSON Data
    3. Extracting Data from HTML
    4. Code-Generating Code
    5. Impact Analysis: The Second Case Study
      1. Gather Together Every Code “Artifact” You Can
      2. Import Artifacts into SQL
      3. And Now, for My Next Trick
    6. Final Thoughts on Code As Data
    7. Final Thoughts on All of It
  18. Appendix. The Data “Model”
    1. Customer Table
    2. NormalizedCustomer View
    3. PotentialMatches Table
    4. CustomerCountByState View
    5. PostalAbbreviations Table
  19. Glossary
  20. Index
  21. About the Author

Product information

  • Title: Fuzzy Data Matching with SQL
  • Author(s): Jim Lehmer
  • Release date: October 2023
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098152277