sql - Using LIKE clause when formats are different

Question

I was given a patient list with names and I am trying to match with a list already in our database and am having troubles given the format of the name field in the patient list. This list is taken from a web form so people can input names however they want so it does not match up well.

WEBFORM_NAME	PATIENT_NAME
JOHN SMITH	SMITH,JOHN L
SHANNON BROWN	BROWN,SHANNON MARIE

question from:https://stackoverflow.com/questions/65831574/using-like-clause-when-formats-are-different

Answer 1

Not sure if all you need is to get first name, here is the WIldCard expression to get first name

SELECT LEFT(WEBFORM_NAME,CHARINDEX(' ',WEBFORM_NAME)-1) AS FirstName1,
SUBSTRING(PATIENT_NAME,CHARINDEX(',',PATIENT_NAME)+1,(CHARINDEX(' ',PATIENT_NAME)-CHARINDEX(',',PATIENT_NAME))) AS FirstName2
FROM yourTable