Finding Possible Like Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Possible Like Records

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-09-17 : 17:59:59

I have two tables with 3 columns each consisting of first name, last name, and date of service.

I want to compare these two tables on all three of those columns for possible matches. The tables are from different data sources and the names seem to be entered differently from the different sources so when I compare in access its telling me they don't match when they most likely do for example

table 1:
smithabc, john 1/1/01
smith, johnx 1/1/01

I want to be able to catch these as actual matches and not have it say they don't match.

Can someone give me an example of how to do this? I assume using wildcards but I can't seem to find a good example?

Thanks in Advance!
Sherri

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 18:06:43

use substring function

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 18:09:42

What are your fuzzy matching rules?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 18:15:39

This is far from perfect, but maybe it will help get you started:

DECLARE @Yak TABLE (LastName VARCHAR(20), FirstName VARCHAR(20), DOS DATETIME)

INSERT @Yak
SELECT 'smithabc', 'john', '1/1/01'
UNION ALL SELECT 'smith', 'johnx', '1/1/01'
UNION ALL SELECT 'Bob', 'Jones', '1/2/01'
UNION ALL SELECT 'Bob', 'Jonesing', '1/2/01'

SELECT DISTINCT
	*
FROM 
	@Yak AS A
INNER JOIN
	@Yak AS B
	ON A.DOS = B.DOS
	AND 
	(
		A.LastName LIKE B.LastName + '%'
		OR B.LastName LIKE A.LastName + '%'
	)
	AND 
	(
		A.FirstName LIKE B.FirstName + '%'
		OR B.FirstName LIKE A.FirstName + '%'
	)

sross81
Posting Yak Master

228 Posts

Posted - 2008-09-17 : 23:42:08

Thank you for the response. I will try this first thing in the morning at work and see if I can make anything happen :)

My fuzzy matching rules are just similar first name and last name. Like the first 4 letters I guess. I guess there are alot of variables that could make it not work right. I will experiment a little with the ideas you provided.

Thanks so much for sharing your thoughts...

quote:

Originally posted by Lamprey

This is far from perfect, but maybe it will help get you started:

DECLARE @Yak TABLE (LastName VARCHAR(20), FirstName VARCHAR(20), DOS DATETIME)

INSERT @Yak
SELECT 'smithabc', 'john', '1/1/01'
UNION ALL SELECT 'smith', 'johnx', '1/1/01'
UNION ALL SELECT 'Bob', 'Jones', '1/2/01'
UNION ALL SELECT 'Bob', 'Jonesing', '1/2/01'

SELECT DISTINCT
	*
FROM 
	@Yak AS A
INNER JOIN
	@Yak AS B
	ON A.DOS = B.DOS
	AND 
	(
		A.LastName LIKE B.LastName + '%'
		OR B.LastName LIKE A.LastName + '%'
	)
	AND 
	(
		A.FirstName LIKE B.FirstName + '%'
		OR B.FirstName LIKE A.FirstName + '%'
	)

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-09-18 : 11:14:31

This code works to tell me what is equal. I think I need to find what is not equal now. I want to know which records are in the table
tempHOinNG
but not in table
tempDSHS

Here is my code. I tried putting NOT LIKE but it doesn't like that

select distinct *
from tempDSHS a
join tempHOinNG b on a.dos=b.dos
and
(a.lastname like b.last_name + '%'
or b.last_name like a.lastname + '%'
)
and
(
a.firstname like b.first_name + '%'
or b.first_name like a.firstname + '%'
)

quote:

Originally posted by Lamprey

This is far from perfect, but maybe it will help get you started:

DECLARE @Yak TABLE (LastName VARCHAR(20), FirstName VARCHAR(20), DOS DATETIME)

INSERT @Yak
SELECT 'smithabc', 'john', '1/1/01'
UNION ALL SELECT 'smith', 'johnx', '1/1/01'
UNION ALL SELECT 'Bob', 'Jones', '1/2/01'
UNION ALL SELECT 'Bob', 'Jonesing', '1/2/01'

SELECT DISTINCT
	*
FROM 
	@Yak AS A
INNER JOIN
	@Yak AS B
	ON A.DOS = B.DOS
	AND 
	(
		A.LastName LIKE B.LastName + '%'
		OR B.LastName LIKE A.LastName + '%'
	)
	AND 
	(
		A.FirstName LIKE B.FirstName + '%'
		OR B.FirstName LIKE A.FirstName + '%'
	)

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-09-18 : 11:18:51

Nevermind I think I figured out the not like thing I just was missing a join here is what I did if anyone was following:

select distinct *
from tempDSHS a
join tempHOinNG b on a.dos=b.dos
and
(a.lastname like b.last_name + '%'
or b.last_name like a.lastname + '%'
)
and
(
a.firstname like b.first_name + '%'
or b.first_name like a.firstname + '%'
)

quote:
Originally posted by sross81

This code works to tell me what is equal. I think I need to find what is not equal now. I want to know which records are in the table
tempHOinNG
but not in table
tempDSHS

Here is my code. I tried putting NOT LIKE but it doesn't like that

select distinct *
from tempDSHS a
join tempHOinNG b on a.dos=b.dos
and
(a.lastname like b.last_name + '%'
or b.last_name like a.lastname + '%'
)
and
(
a.firstname like b.first_name + '%'
or b.first_name like a.firstname + '%'
)

quote:
Originally posted by Lamprey

This is far from perfect, but maybe it will help get you started:
DECLARE @Yak TABLE (LastName VARCHAR(20), FirstName VARCHAR(20), DOS DATETIME)

INSERT @Yak
SELECT 'smithabc', 'john', '1/1/01'
UNION ALL SELECT 'smith', 'johnx', '1/1/01'
UNION ALL SELECT 'Bob', 'Jones', '1/2/01'
UNION ALL SELECT 'Bob', 'Jonesing', '1/2/01'

SELECT DISTINCT
*
FROM
@Yak AS A
INNER JOIN
@Yak AS B
ON A.DOS = B.DOS
AND
(
A.LastName LIKE B.LastName + '%'
OR B.LastName LIKE A.LastName + '%'
)
AND
(
A.FirstName LIKE B.FirstName + '%'
OR B.FirstName LIKE A.FirstName + '%'
)

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2008-09-18 : 11:34:29

Is there a way to do a compare of Like the first 3 or 4 characters???

select distinct a.first_name,a.last_name,a.dob,a.dos
from tempHoinNG a
join tempDSHS b on a.dos=b.dos and a.dob=b.dob
and
(a.last_name not like b.lastname + '%'
or b.lastname not like a.last_name + '%'
)
and
(
a.first_name not like b.firstname + '%'
or b.firstname not like a.first_name + '%'
)

quote:
Originally posted by sross81

Nevermind I think I figured out the not like thing I just was missing a join here is what I did if anyone was following:

select distinct *
from tempDSHS a
join tempHOinNG b on a.dos=b.dos
and
(a.lastname like b.last_name + '%'
or b.last_name like a.lastname + '%'
)
and
(
a.firstname like b.first_name + '%'
or b.first_name like a.firstname + '%'
)

quote:
Originally posted by sross81

This code works to tell me what is equal. I think I need to find what is not equal now. I want to know which records are in the table
tempHOinNG
but not in table
tempDSHS

Here is my code. I tried putting NOT LIKE but it doesn't like that

select distinct *
from tempDSHS a
join tempHOinNG b on a.dos=b.dos
and
(a.lastname like b.last_name + '%'
or b.last_name like a.lastname + '%'
)
and
(
a.firstname like b.first_name + '%'
or b.first_name like a.firstname + '%'
)

quote:
Originally posted by Lamprey

This is far from perfect, but maybe it will help get you started:
DECLARE @Yak TABLE (LastName VARCHAR(20), FirstName VARCHAR(20), DOS DATETIME)

INSERT @Yak
SELECT 'smithabc', 'john', '1/1/01'
UNION ALL SELECT 'smith', 'johnx', '1/1/01'
UNION ALL SELECT 'Bob', 'Jones', '1/2/01'
UNION ALL SELECT 'Bob', 'Jonesing', '1/2/01'

SELECT DISTINCT
*
FROM
@Yak AS A
INNER JOIN
@Yak AS B
ON A.DOS = B.DOS
AND
(
A.LastName LIKE B.LastName + '%'
OR B.LastName LIKE A.LastName + '%'
)
AND
(
A.FirstName LIKE B.FirstName + '%'
OR B.FirstName LIKE A.FirstName + '%'
)

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources