Fuzzy Data Matching with SQL

Appendix. The Data “Model”

I put “model” in quotes because it is so simple I am almost embarrassed. Almost. But I dislike those tech books that build some huge, fake application that you have to understand as well as the subject itself, so I chose to go simple to keep the actual topic in the forefront:

Customer Table

Customer is the main “production” table in our crm system (see Table A-1):

SELECT TOP 10 * FROM crm.Customer;

NormalizedCustomer View

This view implements some of the data normalization techniques used in the book (see Table A-2):

SELECT TOP 10 * FROM crm.NormalizedCustomer;

PotentialMatches Table

This is an “import” table that is used in various examples to attempt to match against the CRM data (see Table A-3):

SELECT TOP 10 * FROM dbo.PotentialMatches;

Table A-1. Customer

	id	first​_name	last​_name	company​_name	address	city	county	state	zip	country	phone1	phone2	email	web​_address
0	1	NULL	NULL	Acme Corp	123 Snell Ave	Jefferson City	Cole	MO	65101	U.S.A.	NULL	573-555-3256	NULL	www​.acme​.test
1	2	NULL	NULL	Foo, Inc.	457 Prairie View St	Boulder	Boulder	CO	80301	US	NULL	303-555-5623	NULL	www​.foo​.test
2	3	NULL	NULL	Snedley & Sons, L.L.C.	443 Arroyo Rd	Raton	Colfax	NM	87740	U.S.	NULL	575-555-0956	NULL	www​.snedley​.test
3	4	Johnetta	Abdallah	NULL	1088 Pinehurst St	Chapel Hill	Orange	NC	27514	United States	919-555-9345	919-555-3791	johnetta​_abdallah​@example​.com	NULL
4	5	Geoffrey	Acey	NULL	7 West Ave #1	Palatine	Cook	IL	60067	USA	847-555-1734 ...