Possible solutions for partial match (at least p out of n fields matches)

user_1408361 0
Viewed 39

I'm looking for a possible solution for partial match/possible match (at least p out of n fields matches).

We have a database (Oracle) filled with person records as follows:

  • firstName,
  • lastName,
  • birthDate,
  • citizenNumber,
  • citizenPlace,
  • birthPlace,
  • anotherNumber,
  • district.

(*) Bear in mind that there is no unique identifier and not all fields can be provided, some fields could be optional (null).

We are looking for a solution to check a given person against the database records, if there is at least 5 out of 8 fields matches.

Is it possible to have a solution using SQL, SQL/Java or should we look for nosql or elastic search solution?

Thank you in advance.

oracle sql java
1 Answers

You can use where and count the matches:

select t.*
from t
where ( (case when t.firstname = :firstname then 1 else 0 end) +
        (case when t.firstname = :lastname then 1 else 0 end) +
        . . .
      ) >= 5;
user_11440350
Related