如何在存储过程中使用LIKE运算符替代IN运算符处理多逗号分隔字符串

First, let's align the SQL logic with your original C# LINQ behavior: you want to check if orderMaster.OrderNumber contains any of the substrings from the comma-separated @OrderNumber parameter. The IN operator checks for exact matches, so we need to switch to a pattern matching approach with LIKE, paired with STRING_SPLIT (a built-in SQL Server function, so no custom functions needed) and EXISTS to handle multiple substrings cleanly.

Step-by-Step Replacement #

For each filter parameter where you want to replace IN with a "contains" LIKE check, use this pattern:

Original IN Condition (for OrderNumber):

AND (@OrderNumber IS NULL OR @OrderNumber = '' OR orderMaster.OrderNumber IN (select * from string_split(@OrderNumber,',')))

Updated LIKE + EXISTS Condition:

AND (@OrderNumber IS NULL OR @OrderNumber = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@OrderNumber, ',') AS s
    WHERE s.value <> '' -- Exclude empty substrings from invalid input (e.g., ",123,")
      AND orderMaster.OrderNumber LIKE '%' + s.value + '%'
))

Full Updated WHERE Clause #

Here's how your entire WHERE block will look with all filters converted to use LIKE (adjust only the ones you need—if some still need exact IN matches, keep those as-is):

-- Where conditions
(@ACCOUNT IS NULL OR @Account = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@Account, ',') AS s
    WHERE s.value <> '' 
      AND partners.PartnerCode LIKE '%' + s.value + '%'
))
-- Multi-select filters started
AND (@OrderNumber IS NULL OR @OrderNumber = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@OrderNumber, ',') AS s
    WHERE s.value <> '' 
      AND orderMaster.OrderNumber LIKE '%' + s.value + '%'
))
AND (@Carrier IS NULL OR @Carrier = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@Carrier, ',') AS s
    WHERE s.value <> '' 
      AND carrier.Description LIKE '%' + s.value + '%'
))
AND (@ItemCode IS NULL OR @ItemCode = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@ItemCode, ',') AS s
    WHERE s.value <> '' 
      AND itemMaster.ItemCode LIKE '%' + s.value + '%'
))
AND (@OrderType IS NULL OR @OrderType = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@OrderType, ',') AS s
    WHERE s.value <> '' 
      AND orderMaster.OrderType LIKE '%' + s.value + '%'
))
AND (@PONumber IS NULL OR @PONumber = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@PONumber, ',') AS s
    WHERE s.value <> '' 
      AND orderMaster.PONumber LIKE '%' + s.value + '%'
))
AND (@SONumber IS NULL OR @SONumber = '' OR EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@SONumber, ',') AS s
    WHERE s.value <> '' 
      AND orderMaster.SONumber LIKE '%' + s.value + '%'
))

Key Notes #

• Why EXISTS?: It efficiently checks if at least one matching substring exists, which mirrors your C# Any() logic perfectly.
• Empty Substring Filter: The s.value <> '' check prevents unexpected matches if your parameter has leading/trailing commas or empty values (e.g., ",1330," would otherwise generate an empty substring that matches every record with LIKE '%%').
• Compatibility: STRING_SPLIT works in SQL Server 2016 and later. If you're on an older version, you'd need a custom split function, but since you mentioned avoiding custom functions, this is the cleanest built-in option.

内容的提问来源于stack exchange，提问作者Mudassar Iqbal