Select and order by most matches

Question

Let's say I explode a string passed in a search. Example: "if there was a dog" "if there were a dog" (silly American).

We explode based on " " so results...

if
there
were
a
dog

Now I want to run a SQL select * from table_name query where column_name like '%something%' or column_name like '%somethingelse%'...

I'm trying to determine how I can search a table and order by rows that contain the most matches. (ie, if row 45 contained 4 of the above split items and row 21 only contained 2, row 45 should be displayed at top in the results).

This would be a primitive "search relevance" logic. Is there a specific term for this kind of retrieval in SQL?

Suggestions?

Answer 1

Just put the comparisons in the order by clause, use case statements to convert them to 0/1, and add them up:

select *
from table_name query
where column_name like '%something%' or column_name like '%somethingelse%'
order by ((case when column_name like '%something%' then 1 else 0 end) +
          (case when column_name like '%somethingelse%' then 1 else 0 end)
          . . .
         ) desc

I would be inclined to write the query as:

select (Match1+Match2+. . .) as NumMatches, <rest of columns>
from (select t.*,
             (case when column_name like '%something%' then 1 else 0 end) as Match1,
             . . .
      from tablename
     ) t
order by NumMatches desc