April 16, 2012
Count all matched fields in mysql query
Question by Ievgenii Fedorenko
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 nick@gmail.com 3
2. 5 Nick nick@yahoo.com 1
3. 2 David david@gmail.com 2
Thank you!!
Answer by Starx
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%'
