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.

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

Publisher resources

View/Submit Errata

Table of contents

  1. I. Pre Ramble
  2. Preface
    1. What Problems Are We Trying to Solve?
    2. What Will We Cover?
      1. Review
      2. Various Data Problems
      3. Bringing It Together
      4. Appendixes
    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. Glossary
    9. The Data “Model”
      1. Environment Layout
      2. Customer Table
      3. “Normalized” View
      4. Meet the Snedleys
    10. Using Code Examples
    11. O’Reilly Online Learning
    12. How to Contact Us
    13. Acknowledgements
  3. II. Review
  4. 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
  5. 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. Cryptpographic Functions
      1. HASHBYTES
    4. Date and Time Functions
      1. GETDATE
      2. DATEADD
      3. DATEDIFF
      4. DATEPART
      5. ISDATE
    5. Logical Functions
      1. 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 and ISNUMERIC
    8. Final Thoughts on Functions
  6. III. Various Data Problems
  7. 3. Names, Names, Names
    1. What’s In A Name?
    2. Last Names
      1. Punctuation
      2. Suffixes
    3. First Names
      1. Last Thoughts About First Names
    4. Middle Name
    5. Nicknames
    6. Company Name
    7. Full Name
      1. Log Files
    8. “Person-Like Entities”
    9. Final Thoughts on Names
  8. 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
  9. 5. Dates, Dates, Dates
    1. Time Is Relative
    2. Final Thoughts On Dates
  10. 6. Email
    1. What Makes A Valid Email Address?
    2. Final Thoughts on Email
  11. 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)
  12. 8. Bad Characters
    1. Data Representations
    2. Invisible Whitespace
    3. COLLATE
    4. Cleaning Up The Input Data
    5. Final Thoughts On Bad Characters
  13. 9. Orthogonal Data
    1. A Common Problem, A Common Solution, A New Common Problem
    2. Lather, Rinse, Repeat
    3. Final Thoughts On Orthogonal Data
  14. IV. Bringing It Together
  15. 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
  16. 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
  17. 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
  18. 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
  19. V. Appendixes
  20. A. The Data “Model”
    1. Customer Table
    2. NormalizedCustomer View
    3. PotentialMatches Table
    4. CustomerCountByState View
    5. PostalAbbreviations Table
  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