如何在CHARINDEX函数中实现多值搜索？附SQL代码调整需求

Absolutely! You can extend your existing SQL logic using CHARINDEX with an OR condition to check for either the comma , or character K. Here's how to modify your code to meet the requirement—if either character exists in context_info, we'll set @OPRID to 'Native SQL'; otherwise, we'll safely extract the substring before the comma (with a fallback to avoid errors):

DECLARE @OPRID CHAR(30) 
SET @OPRID = NULL 

SELECT @OPRID = 
    CASE 
        -- Return 'Native SQL' if either comma or 'K' is present
        WHEN CHARINDEX(',', CAST(context_info AS CHAR(128))) > 0 
             OR CHARINDEX('K', CAST(context_info AS CHAR(128))) > 0 
            THEN 'Native SQL'
        -- Extract substring before comma only if comma exists (avoids invalid index error)
        WHEN CHARINDEX(',', CAST(context_info AS CHAR(128))) > 0 
            THEN SUBSTRING(CAST(context_info AS CHAR(128)), 1, CHARINDEX(',', CAST(context_info AS CHAR(128))) - 1)
        -- Fallback: return the full context_info if neither character is found
        ELSE CAST(context_info AS CHAR(128))
    END 
FROM sys.sysprocesses

Key Changes Explained: #

• Searched CASE Statement: Switched from a simple CASE to a searched one to handle multiple conditional checks easily.
• Multi-Value Check: Added OR CHARINDEX('K', ...) > 0 to detect the presence of the 'K' character alongside the comma.
• Error Prevention: Added a separate condition to only run the substring extraction if the comma exists, and a fallback to return the full context_info when neither character is present—this avoids the invalid substring error that would occur if we tried to extract before a non-existent comma.

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