Get matching part from conditional pattern I'm having a table like this:

|id|key    |value      |
------------------------
| 0|name   |Homer      |
| 1|surname|Simpson    |
| 2|town   |Springfield| 


I'm running this query:

SELECT *
FROM Search
WHERE [value] LIKE '%m%'
OR [value] like '%p%'


Is there any chance to know, which of both conditions did match? 

Within one statement (so not running each of condition separately).

So for example a result like this:

|id|key    |value      |match  | 
--------------------------------
| 0|name   |Homer      |%m%    |
| 1|surname|Simpson    |%m%,%p%|
| 2|town   |Springfield|%p%    |


http://sqlfiddle.com/#!18/bc0b1/3/0

EDIT:
The result may also be like this:

|id|key    |value      |match  | 
--------------------------------
| 0|name   |Homer      |%m%    |
| 1|surname|Simpson    |%m%    |
| 1|surname|Simpson    |%p%    |
| 2|town   |Springfield|%p%    |


EDIT 2:
Conditions might be more than two so a generic approach (if possible) would be great
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...