SQL Case Statement multiple matches

I am trying to make a case statement in SQL for grouping however I am running into an issue where its grouping incorrectly if there's multiple matches.

Is there a way so it searches word by word as opposed to whole thing and gives results by which word shows up first?

Subject Examples:

the animal rode in the car --- EXPECTED RESULT Animal

in the car the animal rode --- EXPECTED RESULT CAR... actual result Animal

CASE 
WHEN Subject like '%animal%' then 'Animal'
WHEN Subject like '%car%' then 'Car'
END as Category 

The problem with using a CASE statement is that the entire test is performed in order. You aren't testing which comes first.

This might look complex but I tried to make it easy for implementation for you.

select *
from (
   select test,Category=vals,position, rn = row_number() over (partition by test order by position)
   from(
      select *
      from (values('the animal rode in the car'),('in the car the animal rode')) as tests(test)
	    cross apply (values('CAR'),('Animal')) as Lookfor(vals)
	    cross apply (select CHARINDEX(Lookfor.vals,tests.test)) as p(position)
	) a
    where position>0
) rn
where rn.rn=1

First let's look at the innermost joins:

1. The first from looks at items you want to test. This can be translated into a SELECT.
2. Next are the values you want to look up
3. Finds the position

The next innermost query:

1. This basically add a row_runmber to sort records by the smallest position (which came first)
2. Also removed and tests that did not find a match (basically the position of 0).

Finally, the outermost query:

1. Organizes your data and selects only the position that came first.

Results:

in the car the animal rode	CAR	    8	1
the animal rode in the car	Animal	5   1

DbFiddle Demo

Similar to other answers, you can use charindex to determine the index of any matches and take whichever comes first. Of course, this only works if you're always doing like '%something%' comparisons; if your pattern is more complex (e.g. like '%this%and%that%') charindex won't be much help.

This approach puts your rules into a table called MatchLogic, allowing you to easily build up your list of rules without amending the code itself.

declare @testdata table (test nvarchar(64))
insert @testdata (test)
values ('the animal rode in the car')
, ('in the car the animal rode')
, ('the dog rode in the the van')

declare @matchLogic table (partialString nvarchar(32), category nvarchar(32))
insert @matchLogic 
values ('animal', 'Animal')
, ('car', 'Car')

select test, (
	select top 1 category 
	from @matchLogic 
	where test like '%' + partialString + '%' 
	order by charindex(partialString, test) asc
) category
from @testData t

Another approach would be to match against 1 character from your string, then 2, then 3, etc until you get your first match.

Note:

• This is very inefficient, so not suitable for large datasets.
• This gets the first full match; not the match whose first character comes first; e.g. see the monkey ... animal ... bicycle example where animal is the first complete match even though monkey comes first, as monkey ... bicycle only matches once we have the full string.

declare @testdata table (id bigint identity(1,1),test nvarchar(64))
insert @testdata (test)
values ('the animal rode in the car')
, ('in the car the animal rode')
, ('the monkey rode the bicycle')
, ('the monkey (an animal) rode the bicycle')
, ('the dog rode in the the van')

declare @matchLogic table (pattern nvarchar(32), category nvarchar(32))
insert @matchLogic 
values ('%animal%', 'Animal')
, ('%car%', 'Car')
, ('%monkey%bicycle%', 'MonkeyBike')

; with substrings as (
	select id, test part, len(test) chars
	from @testdata 
	union all 
	select id, substring(part, 1, chars-1), chars-1
	from substrings s
	where chars > 1
)
select t.id, t.test, x.category
from @testData t
outer apply (
	select top 1 category
	from @matchLogic m
	inner join substrings s
	on part like m.pattern
	where s.id = t.id
	order by s.chars asc
) x

You are mistaken on how it checks. The first when clause will get checked first, if it matches, the second one will not get evaluated. Both of your examples match the first when, so that is what is getting triggered.

This should give you what you want:

case 
 when Subject  like '%animal%' and (Subject not like '%car%' OR  CHARINDEX('animal',Subject,1) <= CHARINDEX('car',Subject,1)) then 'Animal'
 when Subject  like  '%car%'  and (Subject not like '%animal%' OR  CHARINDEX('car',Subject,1) <= CHARINDEX('animal',Subject,1))  then 'Car' 
 else 'UNKNOWN'
 end AS Category,