SQL: Combining LIKE and IN in SQL , and Showing the Array of LIKE in a column

Question

Below code works to combine Like and IN. But I need a column in the output with the values that I used for the Like:

SELECT file_id
FROM   table1 as t
WHERE  archival_date = '20180108'
WHERE  EXISTS(SELECT *
              FROM   (VALUES ('%portfolo1filename%'),
                             ('%portfolo2filename%'),
                             ('%portfolo3filename%')) Vals(val)
              WHERE  filename LIKE '%' + val + '%')  

What I need as Output:

____________________________
val                  file_ID
____________________________
portfolo1filename     2230
portfolo2filename     2240
portfolo3filename     3345        

Current code's Output:

______________
   file_ID
______________
     2230
     2240
     3345    

Note: val here contains the list of values (i.e filenames) that I need to search as like

table1 has following entries:

---

filename                             file_ID    archival_date 
__________________________________________________________________
Broker1_portfolo1filename             2230         20180108
Broker1_portfolo2filename             2240         20180108
Broker2_portfolo3filename             3345         20180908
Broker2_portfolo2filename             1345         20180904
Broker3_portfolo1filename             3045         20180906
Broker2_portfolo2filename             3355         20180907
Broker2_portfolo3filename             7340         20180901

Answer 1

One solution would be to convert your WHERE EXISTS clause to an INNER JOIN, like :

SELECT t1.file_id, vals.val
FROM
    table1 t1
    INNER JOIN  (VALUES ('value1'), ('value2'), ('value3')) Vals(val) 
        ON  t1.column1 LIKE '%' + vals.val + '%' 

The downside of this approach is that if multiple expression match the value of column1 in a given record, you will find two rows in the output. To avoid that, you could for example concatenate all succesfully matched expressions in a single field.

If you are using SQL Server 2017 (your query runs fine on this RDBMS), you can use STRING_AGG :

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

Tested in this db fiddle.