Hey experts. I'm working on a project that will consume a CSV. I need to match the records in the CSV with a record in my Employee table. I am using a "key" (provided by the client) to do the matching. What I would like to do is, once I find the Key, inspect the First and Last name fields in the db and compare them to the first and last name fed from the csv. I want a bit more than exact matches (or LIKE) that would return only things that match exactly. I want the comparison to be be a bit flexible and then return a "confidence" index of how close the two match. I've done this as a binary "It Matches" or "It doesn't match" approach like this:
DECLARE @FirstName nvarchar(50)
DECLARE @Lastname nvarchar(50)
DECLARE @EmployeeNumber nvarchar(50)
SET @Firstname = 'Joe'
SET @LastName = 'User'
SET @EmployeeNumber = '01343'
CASE WHEN E.Lastname = @LastName then 1
END as [LastNameConfidence],
CASE WHEN E.Firstname = @Firstname then 1
END As [FirstNameConfidence]
Employee as E
E.EmployeeNumber like @EmployeeNumber
Is there any way I can replace those last two columns of my select with a more grainular inspection of the text? I don't need to search all records in the table (I am going to only do this inspection if the EmployeeNumber matches exactly). I just want to know how close the names match.
For instance, if the CSV contained Joseph User rather than Joe User I want to be able to see that while not exact, the first name is pretty close (containing two consecutive letters to the target record in succession).