How can I use LIKE operator instead of IN operator for multiple commas contains string in stored procedure

Question

I just made the stored procedure for search items before the procedure I was doing this all via LINQ in C# e.g

//orders
                if (objParam.OrderNumber != null && objParam.OrderNumber.Count > 0)
                {
                    foreach (var orderNumber in objParam.OrderNumber)
                    {
                        orderNumbersBuilder.Append(',' + orderNumber.ToString());
                    }
                }

                var orderNbrs = orderNumbersBuilder.ToString().Trim(',').Split(',');    

//Searching
    (objParam.OrderNumber.Count == 0 || orderNbrs.Any(a => i.tblOrderMaster.OrderNumber.Contains(a)))

Now I want to do with the stored procedure. I'm getting the result with IN operator but I want to use LIKE operator e.g

SELECT * FROM tblOrderMaster WHERE TrxNumber LIKE '%' + (SELECT * FROM STRING_SPLIT('1330,1329',',')) + '%'

I've multiple filters so I don't want to use function and subqueries e.g

--Params
    @Account NVARCHAR(MAX) = NULL,
            @OrderNumber NVARCHAR(MAX) = NULL,
            @Carrier NVARCHAR(MAX) = NULL,
            @ItemCode NVARCHAR(MAX) = NULL,
            @OrderType NVARCHAR(MAX) = NULL,
            @PONumber NVARCHAR(MAX) = NULL,
            @SONumber NVARCHAR(MAX) = NULL
--columns start
--columns end

--Where condtions
    (@ACCOUNT IS NULL OR @Account = '' OR partners.PartnerCode IN (select * from string_split(@ACCOUNT,','))) -- multi select filters started
            AND
            (@OrderNumber IS NULL OR @OrderNumber = '' OR orderMaster.OrderNumber IN (select * from string_split(@OrderNumber,',')))
            AND
            (@Carrier IS NULL OR @Carrier = '' OR carrier.Description IN (select * from string_split(@Carrier,',')))
            AND
            (@ItemCode IS NULL OR @ItemCode = '' OR itemMaster.ItemCode IN (select * from string_split(@ItemCode,',')))
            AND
            (@OrderType IS NULL OR @OrderType = '' OR orderMaster.OrderType IN (select * from string_split(@OrderType,',')))
            AND
            (@PONumber IS NULL OR @PONumber = '' OR orderMaster.PONumber IN (select * from string_split(@PONumber,',')))
            AND
            (@SONumber IS NULL OR @SONumber = '' OR orderMaster.SONumber IN (select * from string_split(@SONumber,',')))


    

Answer 1

You would need to use subqueries; the fact you don't want to doesn't change this with your current design. Using the query with the literal values you have, it would look like this:

SELECT *
FROM dbo.tblOrderMaster OM
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT('1330,1329', ',') SS
              WHERE OM.TrxNumber LIKE '%' + SS.[Value] + '%')

If you really don't want to use subqueries, then use table type parameters and then you can perform a JOIN:

SELECT OM.*
FROM dbo.tblOrderMaster OM
     JOIN @YourTableVariable YTV ON OM.TrxNumber LIKE '%' + YTV.SearchValue + '%'