SQL功能扩展需求：基于Addon10列实现多图片检测

Hey there! Let's figure out how to extend your existing SQL code to detect if the Addon10 column contains multiple image tags. Based on the format you shared (stacked <img src="..."> elements), the core idea is to count how many times the <img tag (note the trailing space to avoid false matches) appears in the column value.

Key Approach #

We can use string manipulation functions to count occurrences of the <img substring. If the count is 2 or more, we flag it as "Multiple Images"; if exactly 1, it's a "Single Image"; otherwise, we fall back to your existing document/unknown checks.

Example Implementations by SQL Dialect #

MySQL/MariaDB

SELECT
    YourTableID, -- Replace with your actual primary key column
    Addon10,
    CASE
        -- Check for any image tags first (case-insensitive)
        WHEN LOWER(Addon10) LIKE '%<img %' THEN
            CASE
                -- Calculate number of <img > occurrences
                WHEN (LENGTH(LOWER(Addon10)) - LENGTH(REPLACE(LOWER(Addon10), '<img ', ''))) / LENGTH('<img ') >= 2 THEN 'Multiple Images'
                ELSE 'Single Image'
            END
        -- Check for document formats (adjust extensions as needed)
        WHEN Addon10 LIKE '%.docx%' 
             OR Addon10 LIKE '%.pdf%' 
             OR Addon10 LIKE '%.doc%' 
             OR Addon10 LIKE '%.xlsx%' THEN 'Document'
        ELSE 'Unknown Content'
    END AS ContentType
FROM YourTableName; -- Replace with your actual table name

SQL Server

SELECT
    YourTableID, -- Replace with your actual primary key column
    Addon10,
    CASE
        -- Case-insensitive check for image tags
        WHEN Addon10 LIKE '%<img %' COLLATE SQL_Latin1_General_CP1_CI_AS THEN
            CASE
                -- Calculate occurrence count
                WHEN (LEN(Addon10) - LEN(REPLACE(Addon10 COLLATE SQL_Latin1_General_CP1_CI_AS, '<img ', ''))) / LEN('<img ') >= 2 THEN 'Multiple Images'
                ELSE 'Single Image'
            END
        -- Document format checks
        WHEN Addon10 LIKE '%.docx%' 
             OR Addon10 LIKE '%.pdf%' 
             OR Addon10 LIKE '%.doc%' THEN 'Document'
        ELSE 'Unknown Content'
    END AS ContentType
FROM YourTableName;

PostgreSQL

PostgreSQL has a handy regexp_count function that simplifies counting with regex (including case-insensitive matching):

SELECT
    YourTableID, -- Replace with your actual primary key column
    Addon10,
    CASE
        -- Check for image tags (case-insensitive)
        WHEN regexp_count(Addon10, '<img ', 'gi') > 0 THEN
            CASE
                WHEN regexp_count(Addon10, '<img ', 'gi') >= 2 THEN 'Multiple Images'
                ELSE 'Single Image'
            END
        -- Regex-based document check (case-insensitive)
        WHEN Addon10 ~* '\.(docx|pdf|doc|xlsx)$' THEN 'Document'
        ELSE 'Unknown Content'
    END AS ContentType
FROM YourTableName;

Important Notes #

• Case Insensitivity: The examples handle cases where the tag might be <IMG instead of <img by using LOWER() or case-insensitive collations/regex flags. Adjust if your data only uses lowercase tags.
• False Matches: Using <img (with a space) helps avoid matching text like image.png or other strings containing "img" without the tag structure. If your tags might omit the space (e.g., <img src=), adjust the substring to <img instead.
• Document Detection: Modify the document extension checks to match your actual document formats (e.g., add .txt, .pptx as needed).

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