Return to Revisions

A potentially faster method that works with your existing structure would be

DECLARE @Query VARCHAR(8000) = 'car BIKE RACK';

WITH QSplit([1], [2], [3], [4]) As
(
SELECT ' ' + [1], 
       ' ' + [2], 
       ' ' + [3], 
       ' ' + [4]
FROM string_split(UPPER(@Query), ' ', 1)
PIVOT (MAX(value) FOR ordinal IN ([1], [2], [3], [4])) AS P
)
SELECT m.*, Rank
FROM MyTable m
CROSS JOIN QSplit
CROSS APPLY (VALUES (CONCAT(' ', UPPER(Data) COLLATE Latin1_General_100_BIN2))) V(SpacePadded)
CROSS APPLY (VALUES (IIF(CHARINDEX([1],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([2],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([3],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([4],SpacePadded) > 0,1,0))               
                ) V2(Rank)
WHERE Rank > 0 
ORDER BY Rank DESC;

This

• Defines @Query as a non max datatype as doesn't sound like you need max and that can be slower.
• Only splits @Query once rather than redoing the splitting for each row in MyTable
• Removes the (non inlined) scalar UDF
• Simplifies the calculation of RANK to remove some execution plan operators
• Uses a binary collation in the hope that this should speed up the CHARINDEX search