Comparing a column between two tables with a wildcard

Question

Comparing a column between two tables with a wildcard

j.kalkowski

SSC Rookie

Points: 32
More actions
August 9, 2015 at 1:01 pm

#306582

I have a table containing records of criminal convictions. There are over 1M records and the only change is additions to the table on a monthly basis. The two columns I need to deal with are convicted.NAME and convicted.DOB
I have a second table that has 2 columns. One is the name of the defendant and the other is the birth date. This would be monitor.NAME and monitor.DOB
There are no primary keys or any other way to join the tables for this search I want to do.
I would like to be able to put a name in the "monitor" table and run a query to see if there is a match in the convicted table.
The problem I am having is middle initials or names. If I want to monitor.name = 'SMITH JOHN' it will return the results fine. The problem I am having is if the conviction is in the database as 'SMITH JOHN T', or 'SMITH JOHN THOMAS'.
How can I use the monitor table with a 'LASTNAME FIRSTNAME' and return results if the convicted table has a middle initial. I tried with a JOIN:
select distinct convicted.*
from convicted
join monitor
on monitor.name like convicted.defendant
and monitor.birthdate = convicted.dob

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 1

j.kalkowski (8/9/2015)
I have a table containing records of criminal convictions. There are over 1M records and the only change is additions to the table on a monthly basis. The two columns I need to deal with are convicted.NAME and convicted.DOB
I have a second table that has 2 columns. One is the name of the defendant and the other is the birth date. This would be monitor.NAME and monitor.DOB
There are no primary keys or any other way to join the tables for this search I want to do.
I would like to be able to put a name in the "monitor" table and run a query to see if there is a match in the convicted table.
The problem I am having is middle initials or names. If I want to monitor.name = 'SMITH JOHN' it will return the results fine. The problem I am having is if the conviction is in the database as 'SMITH JOHN T', or 'SMITH JOHN THOMAS'.
How can I use the monitor table with a 'LASTNAME FIRSTNAME' and return results if the convicted table has a middle initial. I tried with a JOIN:
select distinct convicted.*
from convicted
join monitor
on monitor.name like convicted.defendant
and monitor.birthdate = convicted.dob

Are both tables absolutely guaranteed to contain the last name first always and forever?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 2

With the data stored in a denormalized fashion like that, you've really got two choices. The better option, break up the name into multiple columns. However, that's going to be a lot of work and will change the structures. Option 2, you can use string functions to only pull the first word. You can use LEFT and CHARINDEX to look for the space to get the length of the name. However, that's going to cause a major performance hit on your system. It'll run really slow. Option 1 is the best choice even though it's the most painful in the short term.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You have the LIKE statement backwards and you need to supply the wild card like below

select distinct convicted.*

from convicted

join monitor

on convicted.defendant like monitor.name + '%'

and monitor.birthdate = convicted.dob

Or you can include the wild card in the monitor.name column on insert

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

j.kalkowski SSC Rookie Points: 32 More actions · Answer 4

This did it, thanks!

I did try with the wildcard but I had it on the wrong side.

Jack Corbett (8/10/2015)
You have the LIKE statement backwards and you need to supply the wild card like below
select distinct convicted.*
from convicted
join monitor
on convicted.defendant like monitor.name + '%'
and monitor.birthdate = convicted.dob
Or you can include the wild card in the monitor.name column on insert

Eric M Russell SSC Guru Points: 125599 More actions · Answer 5

I answered a similar question last week.

"MATCH NAME IN DIFFERENT TABLES WHERE NAME ORDER IS REVERSED"

http://www.sqlservercentral.com/Forums/FindPost1709265.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho