SSRS selecting results based on comma delimited list with like statement

Question

Based on my question SSRS selecting results based on comma delimited list

Is it possible to do this, but instead of doing this as a an EQUALS, can it be done as below?
WHERE value like 'abc%','def%'

One thing to note is that the % is not included in the list.

Edit your question and provide sample data and desired results. The question is entirely unclear, especially because the referenced question has no `EQUALS`. — Gordon Linoff, Nov 17 '17 at 11:57
@MiguelH A list of comma separated values would be passed in so this wouldn't be possible — peterbonar, Nov 17 '17 at 12:27
@peterbonar Split that into a temporary table/subquery, then `join` the main result set to that `on main.blah like tmp.split`. — underscore_d, Nov 17 '17 at 12:36

score 0 · Answer 1 · answered Nov 17 '17 at 12:34

One option is to split the passed in SSRS variable (CSV) into a table and join on that

DECLARE @tab TABLE (Col1 NVARCHAR(200))
INSERT INTO @tab (Col1)
VALUES (N'abc'),(N'def'),(N'xyz'),(N'nop'),(N'ghi'),(N'lmn')

DECLARE @substrings NVARCHAR(200) = 'abc,def,ghi'

;WITH cteX
AS(   --dynamically split the string
    SELECT Strings = y.i.value('(./text())[1]', 'nvarchar(4000)')
    FROM 
    ( 
    SELECT x = CONVERT(XML, '<i>' 
        + REPLACE(@substrings, ',', '</i><i>') 
        + '</i>').query('.')
    ) AS a CROSS APPLY x.nodes('i') AS y(i)
) 
SELECT
    T.*
FROM @tab   T
INNER JOIN cteX X ON X.Strings = T.Col1

gives the following result

SSRS selecting results based on comma delimited list with like statement

1 Answers1