SQL Server匹配两列多字符串并筛选含共享字符串的记录

安全阿岳

2026-5-26

Alright, let's break down why your original SQL didn't work first, then fix it for you.

The Problem with Your Original Query

Your statement SELECT word1, word2 from Table where word1 like '%'+word2+'%' checks if the entire word2 string is a substring of word1—but that's not what you need. Your goal is to find records where word1 and word2 share at least one independent word (like "dog" in row 1, "tall" in row 2, or "wood" in row 3). The original logic doesn't account for individual word matches, so it misses those cases entirely.

Feasible Solutions by Database

The core idea is to split both word1 and word2 into individual words, then check if there's any overlap between the two sets of words. Here's how to do this in common databases:

1. PostgreSQL (Cleanest Approach)

PostgreSQL has built-in array operators that make this trivial. Use string_to_array to convert each field into an array of words, then use the && operator to check for intersections:

SELECT ID, word1, word2
FROM YourTable
WHERE string_to_array(word1, ' ') && string_to_array(word2, ' ')

2. SQL Server

Use the STRING_SPLIT function to split each field into rows of words, then use an EXISTS subquery to check for matching words:

SELECT ID, word1, word2
FROM YourTable t
WHERE EXISTS (
    SELECT 1
    FROM STRING_SPLIT(t.word1, ' ') s1
    INNER JOIN STRING_SPLIT(t.word2, ' ') s2 
        ON s1.value = s2.value
)

3. MySQL

Option 1: Regular Expression (Quick for Simple Cases)

Use REGEXP with word boundaries to match any shared word. This works in most MySQL versions:

SELECT ID, word1, word2
FROM YourTable
WHERE word1 REGEXP CONCAT('\\b', REPLACE(word2, ' ', '\\b|\\b'), '\\b')

The \\b ensures we match whole words (so "wood" won't accidentally match "wooden").

Option 2: JSON Table (MySQL 8.0+)

For more robust word splitting (especially if you have edge cases like extra spaces), use JSON_TABLE to convert strings into rows:

SELECT ID, word1, word2
FROM YourTable t
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        CONCAT('["', REPLACE(t.word1, ' ', '","'), '"]'),
        '$[*]' COLUMNS(word VARCHAR(255) PATH '$')
    ) s1
    INNER JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.word2, ' ', '","'), '"]'),
        '$[*]' COLUMNS(word VARCHAR(255) PATH '$')
    ) s2 
        ON s1.word = s2.word
)

What These Queries Do

All of these solutions will return exactly the records you want: rows 1, 2, and 3, where word1 and word2 share at least one common word. Rows 4 and 5 (no overlapping words) will be excluded correctly.

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