How to use like function in MySQL or run LIKE command in mysql when search need to more specific

Question

I want to search in database that how much person have name 'i am steven'

I want to search on user table with their three column

firstname secondname lastname

I want to sorting them as First name match comes first secondname latter and lastname last when I see the result.

are their any command in mysql solve this puzzle

Answer 1

select 
if (firstname    like 'i am steven%', 4,
  if(secondname  like 'i am steven%', 2,
    if (lastname like 'i am steven%', 1, 0)
  )
) as first_name_first
from 
 user
where 
  firstname  like 'i am steven%' or
  secondname like 'i am steven%' or
  lastname   like 'i am steven%'
order by first_name_first desc;