SAS PROC SQL：如何快速检测变量是否包含完整子串？

Got it, let's work through this problem step by step to make sure you're reliably matching those target codes, even with messy input like multiple consecutive spaces.

The Core Problem #

Your Code column has values like 1000 1200 A1000 (or sometimes with extra spaces like 1000 A1000 BBB), and you need to check if any of the entries match your Interested_Code list (e.g., 1000, A1000, etc.). The initial approach of wrapping with spaces works for clean data, but falls short when there are multiple spaces between codes.

Step 1: Clean Up Whitespace First #

First, we need to normalize all consecutive spaces to a single space. This ensures every code is separated by exactly one space, making matching consistent. Here's how to do it based on your SQL dialect:

• PostgreSQL/MySQL: Use regex replacement to squash multiple spaces into one:

  REGEXP_REPLACE(A.CODE, '\s+', ' ', 'g')
  

• SQL Server: If you're on 2016+ with compatibility level 130+, you can use REGEXP_REPLACE too. For older versions, a nested REPLACE trick works:

  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(A.CODE, '  ', ' ' + CHAR(7)), CHAR(7) + ' ', ''), CHAR(7), '')))
  

Step 2: Ensure Whole-Code Matches #

Once the whitespace is cleaned, we need to make sure we're matching entire codes (not partial ones like 1000 in 10000). The trick here is to wrap both the cleaned Code column and your target Interested_Code with single spaces. This way, even if the target code is at the start or end of the Code string, it will be surrounded by spaces in our concatenated value.

Putting it all together, here's a full query example (using PostgreSQL syntax):

SELECT 
    A.*,
    T3.Interested_Code
FROM 
    YourTable A
JOIN 
    InterestedCodes T3 
    ON CONCAT(' ', REGEXP_REPLACE(A.CODE, '\s+', ' ', 'g'), ' ') 
       LIKE CONCAT('% ', T3.Interested_Code, ' %');

Breakdown:

1. REGEXP_REPLACE(...): Fixes multiple spaces to single spaces, cleaning up messy input.
2. CONCAT(' ', ..., ' '): Adds leading/trailing spaces to both the cleaned Code and target code. So 1000 A1000 becomes 1000 A1000, and A1000 becomes A1000—ensuring we only match the full code.
3. The LIKE condition: Looks for the target code surrounded by single spaces in our cleaned string, so partial matches are avoided.

Alternative: Split and Match (More Efficient for Large Data) #

If your SQL dialect supports string splitting (like SQL Server 2016+ or PostgreSQL 10+), splitting the cleaned Code into individual rows and doing exact matches can be more efficient:

-- SQL Server example
SELECT 
    A.*,
    T3.Interested_Code
FROM 
    YourTable A
CROSS APPLY 
    STRING_SPLIT(LTRIM(RTRIM(REPLACE(A.CODE, '  ', ' '))), ' ') AS SplitCodes
JOIN 
    InterestedCodes T3 ON SplitCodes.value = T3.Interested_Code;

This splits the cleaned Code into separate rows for each code, then joins directly on exact matches with your Interested_Code list—no fuzzy LIKE needed!

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