mysql regexp for all column with same data

Question

i have a table like this.

 id     name    father_name     age
  1     raja     first          12
  2     second   first          13

When i execute a below query.

SELECT * FROM class WHERE name REGEXP 'first|12'
OR father_name REGEXP 'first|12'
OR age REGEXP 'first|12'

I getting below as a results.

id  name    father_name     age
1   raja     first          12
2   second   first          13

But I want below as a result.

id  name    father_name     age
1   raja     first          12

If I change name with or condition. I can achieve.But As same time the user given raja|12 means

SELECT * FROM class WHERE name REGEXP 'raja|12'
    OR father_name REGEXP 'raja|12'
    OR age REGEXP 'raja|12'

I want the result like this.

id  name    father_name     age
1   raja     first          12

Because i dont know which one will get from user name or father_name or age or all the three. So if i get all the three there is no problem. But when i get a singl or doble values so i need to search regarding that.

Is there any possibility to get those results?

Answer 1

You seem to want and instead of or, but this is complicated by the fact that you don't seem to care about name. I'm tempted to say:

SELECT *
FROM class
WHERE father_name REGEXP 'first|12' AND
      age REGEXP 'first|12';

I'm not sure what name is doing in the WHERE clause.

EDIT:

It occurs to me that you want the best matching row. If so:

SELECT *
FROM class
WHERE name REGEXP 'first|12' OR
      father_name REGEXP 'first|12' OR
      age REGEXP 'first|12'
ORDER BY ((name REGEXP 'first|12') + (father_name REGEXP 'first|12') + (age REGEXP 'first|12')) DESC
LIMIT 1;