Get matching part from conditional pattern
WITH 
cte AS ( SELECT value AS pattern            -- or TRIM(value)
         FROM STRING_SPLIT('%m%,%p%', ',')  -- comma is NOT allowed in a pattern
                                            -- or alter your delimiter
       )
SELECT Search.[id], 
       Search.[key], 
       Search.[value], 
       STRING_AGG(cte.pattern, ',') [match]
FROM Search, 
JOIN cte ON Search.[value] LIKE cte.pattern -- or LEFT JOIN
GROUP BY Search.[id], 
         Search.[key], 
         Search.[value]


fiddle

Possibe CTE replace usable on previous server versions (CHAR(9) is used as a delimiter, @pattern declared before cte must be query parameter transferred from client as literal):

DECLARE @pattern VARCHAR(64) = '%m%'+CHAR(9)+'%p%';

WITH cte AS ( SELECT LEFT(@pattern,CHARINDEX(CHAR(9),@pattern)-1) pattern, 
                     SUBSTRING(@pattern, CHARINDEX(CHAR(9),@pattern)+1, LEN(@pattern)) slack
              UNION ALL
              SELECT LEFT(slack,CHARINDEX(CHAR(9),slack)-1), 
                     SUBSTRING(slack, CHARINDEX(CHAR(9),slack)+1, LEN(slack))
              FROM cte
              WHERE CHARINDEX(CHAR(9),slack) &gt; 0
              UNION ALL
              SELECT slack, ''
              FROM cte
              WHERE CHARINDEX(CHAR(9),slack) = 0
                AND slack != ''
            )


fiddle

STRING_AGG() must be replaced too...