I'm not sure i understand you well...
You need to split
@SPInput
by
{'|', ','}
to get
[Type]
and
[Value]
. Then you have to compare that values with the values in a table. See:
DECLARE @tmp TABLE(ID int, CustomerID int, [Type] varchar(30), [Value] varchar(30))
INSERT INTO @tmp(ID, CustomerID, [Type], [Value])
VALUES(1, 1, 'T1', '123'),
(2, 1, 'T2', 'XYZ'),
(3, 1, 'T1', '456'),
(4, 2, 'T1', '789'),
(5, 1, 'T2', 'ABC'),
(6, 4, 'T1', '111')
DECLARE @SPInput varchar(150) = 'T1,123|T2,ABC|T1,456'
;WITH KeyPairs AS
(
SELECT LEFT(@SPInput, CHARINDEX('|', @SPInput)-1) KeyPair, RIGHT(@SPInput, LEN(@SPInput)-CHARINDEX('|', @SPInput)) Remainder
WHERE CHARINDEX('|', @SPInput)>0
UNION ALL
SELECT LEFT(Remainder, CHARINDEX('|', Remainder)-1) KeyPair, RIGHT(Remainder, LEN(Remainder)-CHARINDEX('|', Remainder)) Remainder
FROM KeyPairs
WHERE CHARINDEX('|', Remainder)>0
UNION ALL
SELECT Remainder KeyPair, NULL Remainder
FROM KeyPairs
WHERE CHARINDEX('|', Remainder)=0
), TV AS
(
SELECT LEFT(KeyPair, CHARINDEX(',', KeyPair)-1) [Type], RIGHT(KeyPair, LEN(KeyPair)-CHARINDEX(',', KeyPair)) [Value]
FROM KeyPairs
WHERE CHARINDEX(',', KeyPair)>0
)
SELECT t1.*
FROM @tmp t1 INNER JOIN TV t2 ON t1.[Type] = t2.[Type] AND t1.[Value] = t2.[Value]
Result:
ID CustomerID Type Value
1 1 T1 123
3 1 T1 456
5 1 T2 ABC
In above example i'm using
CTE[
^] to split input into parts, but you can also use different way:
Splitting Delimited Strings Using XML in SQL Server[
^]
Now, you have to change the code to your needs.
Good luck!
[EDIT]
As to our discussion (in comments) to the condition:
(T1 AND (123 OR 456)) AND (T2 AND BC)
...
The simplest way to filter data based on above condition is to use client code (c#, vb.net, etc.). See:
DataTable tmp = new DataTable();
tmp.Columns.AddRange(new DataColumn[]
{
new DataColumn("ID", typeof(int)),
new DataColumn("CustomerID", typeof(int)),
new DataColumn("Type", typeof(string)),
new DataColumn("Value", typeof(string))
});
tmp.Rows.Add(new object[]{1, 1, "T1", "123"});
tmp.Rows.Add(new object[]{2, 1, "T2", "XYZ"});
tmp.Rows.Add(new object[]{3, 1, "T1", "456"});
tmp.Rows.Add(new object[]{4, 2, "T1", "789"});
tmp.Rows.Add(new object[]{5, 1, "T2", "ABC"});
tmp.Rows.Add(new object[]{6, 4, "T1", "111"});
KeyValuePair<string, string>[] values2find = new KeyValuePair<string, string>[]
{
new KeyValuePair<string, string>("T1", "123"),
new KeyValuePair<string, string>("T2", "BC"),
new KeyValuePair<string, string>("T1", "456")
};
var filter = values2find
.GroupBy(x=>x.Key)
.ToList();
var result = tmp.AsEnumerable()
.Where(x=> filter.All(y=>y.Key==x.Field<string>("Type") && y.Any(z=>z.Value==x.Field<string>("Value"))))
.ToList();
Console.WriteLine($"Found {result.Count()} result(s)");
foreach(var f in result)
Console.WriteLine($"Type: {f.Field<string>("Type")}\tValue:{f.Field<string>("Value")}");
Result:
Found 0 result(s)
For further details, please see:
Enumerable.All<TSource>(IEnumerable<TSource>, Func<TSource,Boolean>) Method (System.Linq) | Microsoft Docs[
^]
Enumerable.Any Method (System.Linq) | Microsoft Docs[
^]