How do I compare strings that are similar in SQL

See more:

SQL

one table I have a person named HARVEY G BRACKETT, this would be first name, middle name, and last name field.

in another table the same person under full name is Harvey Gilbert Bracket. both tables show the exact same data for birth date and death date as well as place of death. So, I know that this is the correct person.

how can I compare with certainty what I would like is do something that compares the full name by the all the parts of table one first name, middle name, last name.

CHARINDEX(a.[First name], b.Name, 1) >0 and CHARINDEX(a.[Middle name], b.Name, len(a.[First name]) + 1) >0 and CHARINDEX(a.[Last name], b.Name, len(a.[Middle name]) + len(a.[First name]) + 2) >0

I want to go through each part of the string without starting back at position 1.

table 1 First name Harvey has 6 letters

if

CHARINDEX(a.[First name], b.Name, 1) >0

is true then start at position 7

table 1 middle name G

if

len(a.[First name]) + 1 >0

is true then start at new position to match last name inside the full name.

full code is listed below in what have you tried section?

What I have tried:

what I have so far, but I am not sure that its correct:

select * FROM table1 AS b
  INNER JOIN table2 AS a
ON CHARINDEX(a.[First name], b.Name, 1) >0 and CHARINDEX(a.[Middle name], b.Name, len(a.[First name]) + 1) >0 and CHARINDEX(a.[Last name], b.Name, len(a.[Middle name]) + len(a.[First name]) + 2) >0

where a.birth_date = b.birth_date and a.death_date = b.death_date