Fuzzy Data Matching with SQL Enhancing Data Quality and Query Performance (Jim Lehmer) (Z-Library)

【One-Line Pitch】 A practical SQL-first guide to reconciling messy, heterogeneous datasets when all you have is SELECT access—ideal for DBAs, analysts, and data engineers who must deduplicate, clean, and match records without leaving the database. 【Book Arc】 - **Opening (~0%–10%)**: Frames the core problem—two related datasets, no shared key, only SQL—and lays out the book's workflow: import, clean, match, score, report. A quick SELECT/join review (Chapter 1) refreshes the patterns used throughout. - **Early (~10%–32%)**: Builds the SQL toolkit (Chapter 2): date, logical, string, and system functions (CHARINDEX, PATINDEX, TRIM, REPLACE, COALESCE, ISDATE, IIF) that later chapters lean on heavily for parsing and normalization. - **Middle (~32%–55%)**: Tackles common dirty-data domains one by one—names (Chapter 3), locations/addresses (Chapter 4), dates (Chapter 5), email (Chapter 6), phone numbers (Chapter 7), and bad characters/encoding (Chapter 8)—showing how to regularize each before matching. - **Late (~55%–80%)**: Moves into matching itself: extracting data from XML/JSON, generating SQL with SQL, preparing datasets for import and merge, and applying data-quality/ETL thinking to find similarities and differences between expressions of the same entity. - **Ending (~80%–100%)**: Focuses on scoring, reporting results, and operationalizing the workflow—turning candidate matches into reviewable output and production-ready views. (Excerpts do not cover the final chapters in detail.) 【Key Takeaways】 - **Fuzzy matching is a pipeline, not a single query** (Opening): import → clean → match → score → report; each stage has its own SQL techniques and failure modes. - **Normalization must precede matching** (Early–Middle): names, addresses, dates, emails, and phones each carry domain-specific traps—suffixes, punctuation, future birthdates, invalid emails, embedded annotations like "Aunt Judy's #". - **String functions are the workhorses** (Early): CHARINDEX, PATINDEX, LEFT/RIGHT/SUBSTRING, TRIM, REPLACE, and TRANSLATE combine to parse and regularize free-text fields. - **COALESCE and outer joins solve "most current value" problems** (Early): when a loan amount lives across four tables in three systems, COALESCE picks the first non-NULL value across joined sources. - **NULL semantics and collation matter** (Early): ANSI_NULLS behavior, EXISTS vs. joins, and case-sensitive collations can silently break matches; choose COLLATE deliberately. - **Bad characters and encoding are real match-killers** (Middle): nonbreaking spaces, invisible characters, and charset mismatches survive TRIM and corrupt comparisons. - **Generate SQL with SQL to scale** (Late): meta-programming queries boosts productivity when matching rules must be applied across many columns or tables. - **Scoring and reporting close the loop** (Late–Ending): match confidence must be surfaced for human review, not just computed. 【Reading Tips】 - **Skim Part I if you know SQL well**—the author says so explicitly; jump to Chapter 2's function reference and return as needed. - **Deep-read Chapters 3–8** (names, locations, dates, email, phone, bad characters); these are the domain-specific heart of the book and where most real-world pain lives. - **Run the code**: examples are working SQL, and the parsing logic (e.g., splitting "Mortimer Snedley, Jr.") is best understood by executing and modifying it. - **Adopt the CTE-to-view pattern** the author uses during EDA—prototype with CTEs, promote to views for production. - **Treat the author's opinions as a starting point**, not gospel; he explicitly invites you to compare approaches and synthesize your own. 【Coverage Limits】 This guide is based on stratified excerpts covering roughly the first half of the book; the later matching, scoring, and reporting chapters are referenced but not detailed in the source material.