如何用单条SQL查询检测格式化文本是否包含指定短语或标签？

Absolutely! You can handle this check in a single SQL query—no need for multiple requests. The key is to split the comma-separated TAGS values into individual entries and check for matches against your processed text, along with the PHRASE column.

Here's how to do it across common SQL dialects:

First, Let's Clarify the Setup #

Your processed text looks like this (lowercase, hyphenated, alphanumeric only):

hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing

And your table structure is:

CREATE TABLE your_table (
    ID INT,
    PHRASE VARCHAR(255),
    TAGS VARCHAR(255)
);

INSERT INTO your_table VALUES
(1, 'London', 'kings-cross,heathrow,camden-town,ealing'),
(2, 'Berlin', 'charlottenburg-wilmersdorf,friedrichshain-kreuzberg');

Solution 1: PostgreSQL #

PostgreSQL makes splitting comma-separated values easy with STRING_TO_ARRAY and UNNEST. We can cross-join with the split tags to check each one:

SELECT EXISTS(
    SELECT 1
    FROM your_table
    -- Split TAGS into individual rows
    CROSS JOIN UNNEST(STRING_TO_ARRAY(TAGS, ',')) AS tag
    WHERE 
        -- Check if PHRASE exists in the processed text (case-insensitive)
        'hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing' ILIKE CONCAT('%', PHRASE, '%')
        -- OR check if any tag exists in the processed text
        OR 'hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing' ILIKE CONCAT('%', tag, '%')
) AS has_match;

This will return true because "ealing" (a tag in row 1) is present in your sample text.

Solution 2: MySQL 8.0+ #

For newer MySQL versions, use JSON_TABLE to split the comma-separated tags into rows:

SELECT EXISTS(
    SELECT 1
    FROM your_table
    CROSS JOIN JSON_TABLE(
        -- Convert TAGS to a JSON array
        CONCAT('["', REPLACE(TAGS, ',', '","'), '"]'),
        '$[*]' COLUMNS(tag VARCHAR(255) PATH '$')
    ) AS tags
    WHERE 
        -- Case-insensitive check for PHRASE
        INSTR(LOWER('hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing'), LOWER(PHRASE)) > 0
        -- Case-insensitive check for any tag
        OR INSTR(LOWER('hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing'), LOWER(tag)) > 0
) AS has_match;

Solution 3: Older MySQL Versions (Pre-8.0) #

If you're stuck with an older MySQL version, you can use a recursive CTE to split the tags:

WITH RECURSIVE split_tags AS (
    SELECT 
        ID,
        PHRASE,
        SUBSTRING_INDEX(TAGS, ',', 1) AS tag,
        SUBSTRING(TAGS, LENGTH(SUBSTRING_INDEX(TAGS, ',', 1)) + 2) AS remaining_tags
    FROM your_table
    WHERE TAGS != ''
    UNION ALL
    SELECT 
        ID,
        PHRASE,
        SUBSTRING_INDEX(remaining_tags, ',', 1),
        SUBSTRING(remaining_tags, LENGTH(SUBSTRING_INDEX(remaining_tags, ',', 1)) + 2)
    FROM split_tags
    WHERE remaining_tags != ''
)
SELECT EXISTS(
    SELECT 1
    FROM split_tags
    WHERE 
        INSTR(LOWER('hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing'), LOWER(PHRASE)) > 0
        OR INSTR(LOWER('hello-i-want-to-rent-my-flat-which-is-in-the-best-district-ealing'), LOWER(tag)) > 0
) AS has_match;

Key Notes #

• Replace your_table with your actual table name, and the sample processed text with your variable/parameter.
• All solutions are case-insensitive, matching your pre-processing step of converting text to lowercase.
• The EXISTS clause ensures the query stops as soon as it finds a match, making it efficient even with larger tables.

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