SQL中LIKE匹配CES缩写误匹配的解决方案咨询

Got it, let's solve this matching issue you're running into. The problem is that your current LIKE '%CES%' rule catches any string containing "CES" (like BraCES), but you need "CES" and "CES-I" to only match as standalone words—while keeping the original contain-matching behavior for terms like Neopla and Metasta.

Here's a targeted solution that adjusts the WHERE clause in your OUTER APPLY to handle CES/CES-I differently:

DECLARE @VTable TABLE (
    ColtoAdj VARCHAR(50),
    HeadlineDescription VARCHAR(50),
    HeadlineDescriptionGroup VARCHAR(50)
); 
INSERT INTO @VTable VALUES 
('Neopla','Neoplasia','Cancer'),
('Metasta','Metastases','Cancer'),
('CES','CES','Cauda Equina'),
('CES-I','CES-I','Cauda Equina');

-- Your main query with adjusted OUTER APPLY
SELECT cmd.*, matched.*
FROM YourMainTable cmd
OUTER APPLY (
    SELECT TOP 1 
        vt.ColtoAdj, 
        vt.HeadlineDescription, 
        vt.HeadlineDescriptionGroup
    FROM @VTable AS vt
    WHERE 
        -- Exact standalone word match for CES/CES-I
        (vt.ColtoAdj IN ('CES', 'CES-I') 
         AND PATINDEX('%[[:space:]]' + vt.ColtoAdj + '[[:space:]]%', ' ' + cmd.Headline + ' ') > 0)
        -- Original contain matching for all other terms
        OR (vt.ColtoAdj NOT IN ('CES', 'CES-I') 
            AND cmd.Headline LIKE '%' + vt.ColtoAdj + '%')
    -- Prioritize exact matches to ensure CES/CES-I takes precedence if multiple matches exist
    ORDER BY CASE WHEN vt.ColtoAdj IN ('CES', 'CES-I') THEN 0 ELSE 1 END
) AS matched;

How this works: #

• Exact matching for CES/CES-I: We wrap the Headline with spaces (' ' + cmd.Headline + ' ') and use PATINDEX to look for the term surrounded by whitespace. This ensures it only matches when "CES" or "CES-I" is a standalone word—whether it's at the start, middle, end of the headline, or the entire headline itself.
• Original logic for other terms: We keep your original LIKE '%' + vt.ColtoAdj + '%' logic so terms like "Neopla" still match "Neoplasia" as expected.
• Priority ordering: The ORDER BY clause ensures that if a headline could match both an exact CES term and another contain term, the exact match is returned first—so you get the precise result you want for CES cases.

Testing edge cases: #

• Headline = 'BraCES': Won't match the CES entry (since "CES" isn't a standalone word here)
• Headline = 'CES': Will match the CES entry (the space-wrapping trick handles single-word headlines)
• Headline = 'CES syndrome': Matches CES
• Headline = 'Cauda Equina CES-I': Matches CES-I
• Headline = 'Neoplasia': Still matches Neopla as before

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