DECLARE @data TABLE (SerialNo VARCHAR(30));
INSERT INTO @data (SerialNo)
VALUES('8018A7400BC3'),
('8018A79B6433'),
('001DD55C6FAB'),
('HDE09B356634'),
('4650C7453C90'),
('3275D8AB544D'),
('4345D8AB544C');
DECLARE @find VARCHAR(30) = '18,8AB';
;WITH CTE AS
(
SELECT LEFT(@find, CHARINDEX(',', @find)-1) AS SerialPart, RIGHT(@find, LEN(@find) - CHARINDEX(',', @find)) AS Remainder
WHERE CHARINDEX(',', @find)>0
UNION ALL
SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SerialPart, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM CTE
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT Remainder AS SerialPart, NULL AS Remainder
FROM CTE
WHERE CHARINDEX(',', Remainder)=0
)
SELECT Orig.*
FROM @data AS Orig INNER JOIN CTE AS Parts ON Orig.SerialNo Like '%' + Parts.SerialPart + '%';
//create sample data
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("SerialNo", typeof(string)));
dt.Rows.Add(new object[]{"8018A7400BC3"});
dt.Rows.Add(new object[]{"8018A79B6433"});
dt.Rows.Add(new object[]{"001DD55C6FAB"});
dt.Rows.Add(new object[]{"HDE09B356634"});
dt.Rows.Add(new object[]{"4650C7453C90"});
dt.Rows.Add(new object[]{"3275D8AB544D"});
dt.Rows.Add(new object[]{"4345D8AB544C"});
string find = @"18,8AB";
var result = dt.AsEnumerable()
.Where(x => find.Split(new string[]{","}, StringSplitOptions.RemoveEmptyEntries).Any(y=> x.Field<string>("SerialNo").Contains(y)))
.ToList();
foreach(DataRow dr in result)
{
Console.WriteLine("{0}", dr.Field<string>("SerialNo"));
}