CODEWITHSUNDEEP
mysql
Evgeny Fedorenko
Evgeny Fedorenko

Reputation: 384

Count all matched fields in mysql query

My question is next: I have a mysql query like this:

SELECT student_id, name, email 
FROM student 
WHERE student_id IN ('1', '2', '3') 
    OR name LIKE '%Nick%' 
    OR email LIKE '%gmail.com%'

How can I get the number of matched fields in a in the form of a column that mysql returns Something like this: 

   ID NAME  EMAIL           MATCHED  
1. 1  Nick  [email protected]  3  
2. 5  Nick  [email protected]  1  
3. 2  David [email protected] 2

Thank you!!

Upvotes: 3

Views: 116

Answers (2)

Starx
Starx

Reputation: 78971

This should work

SELECT student_id, name, email, 
     (
        CASE WHEN student_id IN ('1', '2', '3') THEN 1 END +
        CASE WHEN name LIKE '%Nick%' THEN 1 END +
        CASE WHEN email LIKE '%gmail.com%' THEN 1 END
     ) as matched
FROM student 
WHERE student_id IN ('1', '2', '3') 
    OR name LIKE '%Nick%' 
    OR email LIKE '%gmail.com%'

Upvotes: 2

Marc B
Marc B

Reputation: 360572

It's ugly, but something like

SELECT student_id, name, email,
   IF(student_id IN (1,2,3), 1, 0) +
   IF(name LIKE '%Nick%', 1, 0) +
   IF(email LIKE '%gmail.com%', 1, 0) AS matched
etc...

should do the trick.

Upvotes: 7

Related Questions