Sql Search in a string for multiple words, knowing that some of them might not exist

I have a table full of strings, I want to search for a bunch of words, and retrieve the strings that contains at least 1 of them, for example:

The words I want to search are "cat", "dog" and "bat".

And some strings in a table are:

1  "hi cat, I like dogs and owls"
2  "hi cat"
3  "hey bat, cat, owl, dog"
4  "orange is sweet"

This query should return 1, 2, & 3, but not 4.

I know join might be a solution, but I have no idea how to implement it with more than 6 words for instance.

Edit: Is there a way I can sort the results by the biggest match? meaning I want first results to be the ones that contains most of the keys, if any, that's why I ignored the LIKE-OR combination.

Solution

You can use PATINDEX function, wchich returns index of a match in string. If there's no match it returns 0, so try the following script:

declare @x table(
ID int identity(1,1),
name varchar(30)
)

insert into @x values ('hi cat, I like dogs and owls'), ('hi cat'),('hey bat, cat, owl, dog'),('orange is sweet')

select ID from @x where PATINDEX('%cat%', name) + PATINDEX('%dog%', name) + PATINDEX('%bat%', name)  > 0

Another solution would be using LIKE operator:

select ID from @x where name LIKE '%cat%' or name LIKE '%dog%' or name LIKE '%bat%'

Answering your edit, first approach is the best. You achieve desired result by following query (unfortunately it gets little complicated):

select ID from @x 
where PATINDEX('%cat%', name) + PATINDEX('%dog%', name) + PATINDEX('%bat%', name)  > 0
order by case PATINDEX('%cat%', name) when 0 then 0 else 1 end + 
         case PATINDEX('%dog%', name) when 0 then 0 else 1 end +
         case PATINDEX('%bat%', name) when 0 then 0 else 1 end
desc