How to build a query with sorting by the best occurrence?

Question

SQL

To clarify, I will immediately give an example:
Table:

CREATE TABLE `test` (
  `A` varchar(255) NOT NULL,
  `B` tinyint(1) NOT NULL,
  `C` int(11) NOT NULL,
  `D` int(11) NOT NULL
);

Data:

INSERT INTO `test`(`A`, `B`, `C`, `D`) VALUES ("STR1",true,1,1);
INSERT INTO `test`(`A`, `B`, `C`, `D`) VALUES ("STR2",true,2,2);
INSERT INTO `test`(`A`, `B`, `C`, `D`) VALUES ("STR1",true,2,1);
INSERT INTO `test`(`A`, `B`, `C`, `D`) VALUES ("STR3",false,1,2);
INSERT INTO `test`(`A`, `B`, `C`, `D`) VALUES ("STR4",false,2,2);

The query has 4 variables A = "STR1", B = true, C = 1, D = 1
In the response, all rows sorted in "similarity" to the query.

"STR1",true,1,1    // полное совпадение
"STR1",true,2,1    // 3 совпадение
"STR2",true,2,2    // 1 совпадение
"STR3",false,1,2   // 1 совпадение
"STR4",false,2,2   // нет совпадений

My idea is to count matches and sort by that value.
Write a stored comparison procedure compare(val1, val2) that returns the number 0 or 1
and add them like this...

compare(A, valA) + compare(B, valB) + compare(B, valB)  + compare(D, valD)  AS coincidence

Is it realistic to do it? Any more ideas?

Answer 1

Any other ideas?

SOUNDEX (Transact-SQL) .
And also DIFFERENCE (Transact-SQL) .

Answer 2

So far it's something like this:

SELECT 
  `A`, 
  `B`, 
  `C`, 
  `D`, 
  IF (`A` = "STR1", 1, 0) +
  IF (`B` = true, 1, 0) + 
  IF (`C` = 1, 1, 0) + 
  IF (`D` = 1, 1, 0) 
  AS `CC` 
FROM `test` 
ORDER BY `CC` DESC

Answer 3

Convert all 4 fields to a string, concatenate the received strings, build an index on the received string.