So, I went back to my good old SQL Server, started Query Profiler on the Tridion DB and found my answer.

When using multiple search terms, the SQL IN operator is used, and the Tridion Criteria.Like operator is ignored!!!

In particular look at this snippet from the full code:

    searchArray = Array.ConvertAll(searchArray, x => "%" + x.Trim() + "%");

    var criteria = new List<Criteria>
    {
        new KeywordCriteria(_subjectCategory, searchArray, Criteria.Like),
        new KeywordCriteria(_syllabusCategory, searchArray, Criteria.Like)
    };

As you can see I did the following to set up a LIKE search for each term of the searchArray = { "Physics", "0625" } in this particular case

1. Put "%" wilcard at start and end of each search term
2. Used the Criteria.Like operator on both my criteria

Well, here is what I got in Query Profiler:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000),
                 @P2 nvarchar(4000),@P3 nvarchar(4000),
                 @P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000)',
N'select distinct top 10000 itemmeta0_.PUBLICATION_ID as col_0_0_,  
itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_,
itemmeta0_.TITLE as col_3_0_ from ITEMS itemmeta0_ 
cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ 
where itemmeta0_.PUBLICATION_ID=@P0  
and 
(
relatedkey1_.ITEM_REFERENCE_ID=itemmeta0_.ITEM_REFERENCE_ID 
and relatedkey1_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID 
and relatedkey1_.CATEGORY=@P1 
and (relatedkey1_.KEYWORD in (@P2 , @P3)) 
or 
relatedkey1_.ITEM_REFERENCE_ID=itemmeta0_.ITEM_REFERENCE_ID 
and relatedkey1_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID 
and relatedkey1_.CATEGORY=@P4 
and (relatedkey1_.KEYWORD in (@P5 , @P6))
) order by itemmeta0_.TITLE ASC',
163,N'TSL Subjects',N'%Physics%',N'%0625%',
N'TSLSyllabus',N'%Physics%',N'%0625%'
select @p1

So, Tridion Content has actually broken my query by allowing me to enter wildcards, but then silently substituting the Criteria.Like with a SQL IN operator.

OUR SOLUTION: We are going to restructure our Subject Category to include Subjects + Code, and then do a LIKE on a single search term eg "%Physics 0625%".

Happy to hear if anyone has got any alternative suggestions.

So, I went back to my good old SQL Server, started Query Profiler on the Tridion DB and found my answer.

When using multiple search terms, the SQL IN operator is used, and the Tridion Criteria.Like operator is ignored!!!

In particular look at this snippet from the full code:

    searchArray = Array.ConvertAll(searchArray, x => "%" + x.Trim() + "%");

    var criteria = new List<Criteria>
    {
        new KeywordCriteria(_subjectCategory, searchArray, Criteria.Like),
        new KeywordCriteria(_syllabusCategory, searchArray, Criteria.Like)
    };

As you can see I did the following to set up a LIKE search for each term of the searchArray = { "Physics", "0625" } in this particular case

1. Put "%" wilcard at start and end of each search term
2. Used the Criteria.Like operator on both my criteria

Well, here is what I got in Query Profiler:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 int,@P1 nvarchar(4000),
                 @P2 nvarchar(4000),@P3 nvarchar(4000),
                 @P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000)',
N'select distinct top 10000 itemmeta0_.PUBLICATION_ID as col_0_0_,  
itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_,
itemmeta0_.TITLE as col_3_0_ from ITEMS itemmeta0_ 
cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ 
where itemmeta0_.PUBLICATION_ID=@P0  
and 
(
relatedkey1_.ITEM_REFERENCE_ID=itemmeta0_.ITEM_REFERENCE_ID 
and relatedkey1_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID 
and relatedkey1_.CATEGORY=@P1 
and (relatedkey1_.KEYWORD in (@P2 , @P3)) 
or 
relatedkey1_.ITEM_REFERENCE_ID=itemmeta0_.ITEM_REFERENCE_ID 
and relatedkey1_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID 
and relatedkey1_.CATEGORY=@P4 
and (relatedkey1_.KEYWORD in (@P5 , @P6))
) order by itemmeta0_.TITLE ASC',
163,N'TSL Subjects',N'%Physics%',N'%0625%',
N'TSLSyllabus',N'%Physics%',N'%0625%'
select @p1

So, Tridion Content has actually broken my query by allowing me to enter wildcards, but then silently substituting the Criteria.Like with a SQL IN operator:

and (relatedkey1_.KEYWORD in (@P2 , @P3))

OUR SOLUTION: We are going to restructure our Subject Category to include Subjects + Code, and then do a LIKE on a single search term eg "%Physics 0625%".

Happy to hear if anyone has got any alternative suggestions.