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
Advertisement
Answer
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.