如何检测表A某列字符串是否包含表B某列的任意子串？

Hey there, let's figure out how to fix this matching issue for your tables. I'll break down the problem, share example structures/data, and provide working SQL solutions for common databases.

Problem Recap #

You need to:

• Check if the Group column (which can have strings over 3000 characters) in TableA contains any substring from the Description column in TableB
• Set the Present bit column in TableA to 1 if a match exists, 0 if no matches are found

Your current SQL only matches the first Description entry in TableB—we'll fix that by checking all entries.

Example Table Structures & Data #

TableA (Initial State) #

TableB #

Expected Updated TableA #

Working Solutions by Database #

1. SQL Server #

Use an EXISTS subquery to check every Description in TableB. This works even for NVARCHAR(MAX) columns (your 3000+ character strings):

UPDATE TableA
SET Present = CASE
    WHEN EXISTS (
        SELECT 1
        FROM TableB
        WHERE TableA.[Group] LIKE '%' + TableB.Description + '%'
    ) THEN 1
    ELSE 0
END

Note: If Description contains wildcard characters like % or _, escape them to avoid false matches:

UPDATE TableA
SET Present = CASE
    WHEN EXISTS (
        SELECT 1
        FROM TableB
        WHERE TableA.[Group] LIKE '%' + 
              REPLACE(REPLACE(TableB.Description, '%', '[%]'), '_', '[_]') + 
              '%'
    ) THEN 1
    ELSE 0
END

2. MySQL #

For MySQL's TEXT type (supports long strings), you can use a LEFT JOIN with a distinct match set or an EXISTS clause:

Option 1: Using LEFT JOIN

UPDATE TableA
LEFT JOIN (
    SELECT DISTINCT a.ID
    FROM TableA a
    JOIN TableB b ON a.`Group` LIKE CONCAT('%', b.Description, '%')
) AS matches ON TableA.ID = matches.ID
SET Present = CASE WHEN matches.ID IS NOT NULL THEN 1 ELSE 0 END;

Option 2: Using EXISTS

UPDATE TableA a
SET Present = EXISTS (
    SELECT 1
    FROM TableB b
    WHERE LOCATE(b.Description, a.`Group`) > 0
);

3. PostgreSQL #

PostgreSQL's TEXT type handles long strings seamlessly. Use EXISTS with string concatenation for the LIKE condition:

UPDATE TableA a
SET Present = CASE
    WHEN EXISTS (
        SELECT 1
        FROM TableB b
        WHERE a."Group" LIKE '%' || b.Description || '%'
    ) THEN TRUE
    ELSE FALSE
END;

Key Notes #

• Avoid single-row matching: The core issue with your original query was likely only checking the first row in TableB—EXISTS or a proper join ensures we check every Description entry.
• Long string support: All solutions work with columns that store strings over 3000 characters, as long as you're using the database's appropriate large string type (e.g., NVARCHAR(MAX), TEXT).
• Wildcard safety: If your Description values might include %, _, or other special characters, always escape them to prevent unintended pattern matching.

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