如何使用SQL从comments列提取quality与detail字段并兼容无匹配内容场景

Absolutely! You can definitely use SUBSTRING (paired with functions to locate your target keywords) to pull off this task, and we’ll make sure to handle cases where the quality or detail keywords don’t exist in the comments column by returning NULL—no broken queries or unexpected values here.

First, let’s assume your keywords follow a consistent pattern (like quality: or detail:; adjust these to match your actual keyword formatting). Below are examples for the most common SQL databases:

SQL Server #

In SQL Server, CHARINDEX finds the starting position of your keyword, and we calculate the length needed to capture everything after the keyword:

SELECT
    comments,
    -- Extract content after "quality:"
    CASE
        WHEN CHARINDEX('quality:', comments) > 0 THEN
            SUBSTRING(
                comments,
                CHARINDEX('quality:', comments) + LEN('quality:'),
                LEN(comments) - (CHARINDEX('quality:', comments) + LEN('quality:')) + 1
            )
        ELSE NULL
    END AS quality,
    -- Extract content after "detail:"
    CASE
        WHEN CHARINDEX('detail:', comments) > 0 THEN
            SUBSTRING(
                comments,
                CHARINDEX('detail:', comments) + LEN('detail:'),
                LEN(comments) - (CHARINDEX('detail:', comments) + LEN('detail:')) + 1
            )
        ELSE NULL
    END AS detail
FROM your_table_name;

MySQL #

MySQL simplifies things with SUBSTRING(string FROM start)—it automatically captures everything from the start position to the end of the string. We use LOCATE to find the keyword position:

SELECT
    comments,
    -- Extract content after "quality:"
    IF(LOCATE('quality:', comments) > 0,
       SUBSTRING(comments FROM LOCATE('quality:', comments) + LENGTH('quality:')),
       NULL) AS quality,
    -- Extract content after "detail:"
    IF(LOCATE('detail:', comments) > 0,
       SUBSTRING(comments FROM LOCATE('detail:', comments) + LENGTH('detail:')),
       NULL) AS detail
FROM your_table_name;

PostgreSQL #

PostgreSQL uses POSITION to locate keywords, and like MySQL, SUBSTRING(string FROM start) handles the end-of-string capture automatically:

SELECT
    comments,
    -- Extract content after "quality:"
    CASE
        WHEN POSITION('quality:' IN comments) > 0 THEN
            SUBSTRING(comments FROM POSITION('quality:' IN comments) + LENGTH('quality:'))
        ELSE NULL
    END AS quality,
    -- Extract content after "detail:"
    CASE
        WHEN POSITION('detail:' IN comments) > 0 THEN
            SUBSTRING(comments FROM POSITION('detail:' IN comments) + LENGTH('detail:'))
        ELSE NULL
    END AS detail
FROM your_table_name;

Key Notes: #

• Adjust Keywords: If your keywords are formatted differently (e.g., quality with a space, or capitalized Quality), update the keyword string in the code.
• Case Insensitivity: If you need to match keywords regardless of case, wrap comments in LOWER() (or UPPER()) when checking position. For example, in SQL Server: CHARINDEX('quality:', LOWER(comments)).
• Your Example Data: The sample comments you provided doesn’t contain quality or detail keywords—running any of these queries will return NULL for both quality and detail, which is exactly the behavior you want.

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