SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with table1 As (
  select 'value1 bla' column1
  UNION ALL select '2'
  UNION ALL select '3'
)
SELECT *
FROM   table1
WHERE  EXISTS(SELECT *
              FROM   (VALUES ('value1'),
                             ('value2'),
                             ('value3')) Vals(val)
              WHERE  column1 LIKE '%' + val + '%')  


column1


value1 bla


with table1 As (
  select 'value1 bla' column1, 'file1' file_id
  UNION ALL select '2', 'file2'
  UNION ALL select '3', 'file3'
)
SELECT t1.file_id, vals.val
FROM   table1 t1
INNER JOIN  (VALUES ('value1'), ('value2'), ('value3')) Vals(val) 
    ON  t1.column1 LIKE '%' + vals.val + '%' 


file_id
val


file1
value1


with table1 As (
  select 'value1 bla value2' column1, 'file1' file_id
  UNION ALL select '2', 'file2'
  UNION ALL select '3', 'file3'
)
SELECT t1.file_id, STRING_AGG(vals.val, ',')
FROM   table1 t1
INNER JOIN  (VALUES ('value1'), ('value2'), ('value3')) Vals(val) 
    ON  t1.column1 LIKE '%' + vals.val + '%' 
GROUP BY t1.file_id


file_id
(No column name)


file1
value1,value2