book

Fuzzy Data Matching with SQL

Name: Fuzzy Data Matching with SQL
Author: Jim Lehmer
ISBN: 9781098152277

by Jim Lehmer

October 2023

Intermediate to advanced

282 pages

6h 32m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Preface
What Problems Are We Trying to Solve?What Will We Cover?Part I: ReviewPart II: Various Data ProblemsPart III: Bringing It TogetherAppendixWho Is This Book For?Why SQL?Warning! Opinions Ahead!Typographical Conventions Used in This BookAdditional Information on the Book’s ConventionsThe Data “Model”Environment LayoutCustomer Table“Normalized” ViewMeet the SnedleysUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
I. Review
1. A SELECT Review
Simple SELECT StatementsCommon Table ExpressionsIn CASE of EmergencyJoinsA Diversion into NULL ValuesOUTER JOINsFinding the Most Current ValueFinal Thoughts on SELECT
2. Function Junction
Aggregate FunctionsMAXMINCOUNTSUMAVGConversion FunctionsCAST and CONVERTCOALESCETRY_CONVERTCryptographic Functions: HASHBYTESDate and Time FunctionsGETDATEDATEADDDATEDIFFDATEPARTISDATELogical Functions: IIFString FunctionsCHARINDEX and PATINDEXLENLEFT, RIGHT, and SUBSTRINGLTRIM, RTRIM, and TRIMLOWER and UPPERREPLACE and TRANSLATEREVERSESTRING_AGGSystem FunctionsISNULLISNUMERICFinal Thoughts on Functions
II. Various Data Problems
3. Names, Names, Names
What’s in a Name?Last NamesPunctuationSuffixesFirst NamesMiddle NameNicknamesCompany NameFull Name“Person-Like Entities”Final Thoughts on Names
4. Location, Location, Location
What Makes an Address?Street AddressBox, Suite, Lot, or Apartment NumberDon’t Overdo It!CityCountyState or State AbbreviationZIP or Postal CodeCountryFinal Thoughts on Locations
5. Dates, Dates, Dates
Time Is RelativeFinal Thoughts on Dates
6. Email
What Makes a Valid Email Address?Final Thoughts on Email
7. Phone Numbers
What Makes a “Phone Number”?One Final Note on Tax IDsFinal Thoughts on Phone Numbers (and Tax IDs)

8. Bad Characters
Data RepresentationsInvisible WhitespaceCOLLATECleaning Up the Input DataFinal Thoughts on Bad Characters
9. Orthogonal Data
A Common Problem, A Common Solution, A New Common ProblemLather, Rinse, RepeatFinal Thoughts on Orthogonal Data
III. Bringing It Together
10. The Big Score
What Will We Want?Tuning ScoresEliminating DuplicatesDuplicate DataDuplicated DataFinal Thoughts on Scoring
11. Data Quality, or GIGO
Sneaking Data Quality InImpossible DataSimply WrongSemantically WrongETL Your Way to SuccessFinal Thoughts on Data Quality
12. Tying It All Together
ApproachWhat’s the Score?First Pass: Naive MatchingSecond Pass: Normalizing RelationsImpossible DataNow Let’s NormalizeThird Pass: Score!What About Tuning?Final Thoughts on Practical Matters
13. Code Is Data, Too!
Working with XML DataWorking with JSON DataExtracting Data from HTMLCode-Generating CodeImpact Analysis: The Second Case StudyGather Together Every Code “Artifact” You CanImport Artifacts into SQLAnd Now, for My Next TrickFinal Thoughts on Code As DataFinal Thoughts on All of It
Appendix. The Data “Model”
Customer TableNormalizedCustomer ViewPotentialMatches TableCustomerCountByState ViewPostalAbbreviations Table
Glossary
Index
About the Author