How to make a a query of similarity percentage in SQL Server?

Question

I have a process that before I submit the data to database I have to check whether the data is already exist in the database or not. And if its found the similarity, I want to show the percentage of similarity of it. I have a query like :

SELECT [NAME]
      ,[IDENTITY_NUMBER]
      ,[BIRTHDATE]
      ,[SEX]
FROM [USER]
WHERE [NAME] = @NAME
OR [IDENTITY_NUMBER] = @IDENTITY_NUMBER
OR [BIRTHDATE] = @BIRTHDATE
OR [SEX] = @SEX

I want to make if only the name is similar between the name that I input and the name that exist in the database, it shows 25% similar. if only the name and the birthdate, then 50%. if all is similar, then 100%. Do you know how?

Answer 1

Assuming the match percentage is based on number of properties matched, you can use case:

select [NAME],
    [IDENTITY_NUMBER],
    [BIRTHDATE],
    [SEX],
    case when [NAME] = @NAME then 25 else 0 end
    + case when [IDENTITY_NUMBER] = @IDENTITY_NUMBER then 25 else 0 end
    + case when [BIRTHDATE] = @BIRTHDATE then 25 else 0 end
    + case when [SEX] = @SEX then 25 else 0 end as match_percentage
from [USER]
where [NAME] = @NAME
    or [IDENTITY_NUMBER] = @IDENTITY_NUMBER
    or [BIRTHDATE] = @BIRTHDATE
    or [SEX] = @SEX