Mysql query for searching a value, add weightage on the number of occurances and sort based on the weight-age

Mysql query for searching a value, add weightage on the number of occurances and sort based on the weightage

Consider a table named 'data' which has columns named 'Name', and 'Content', We need to search these columns, if 'Name' colomn has a match it should have a weightage of '.08' and if 'Content' has a match it should have a weightage of '0.2' ,

Suppose 'Name' has one match and 'Content' has 3 match, then it should have a total weightage of (.08*1)+(.02*3) = .14

For that we can use the following query to get the result.

SELECT *
FROM (
SELECT * , (
(
length( `Content` ) - length( replace( LOWER(`Content`) , "SEARCHKEY", "" ) ) ) / length("SEARCHKEY" ) * .08
) + (
(
length( `Content` ) - length( replace( LOWER(`Content`) ,"SEARCHKEY", "" ) ) ) / length("SEARCHKEY" ) * .02
) AS weight
FROM data
)a
WHERE weight > 0
ORDER BY weight DESC
LIMIT 0,10