MS-SQL/TSQL中判断消息内容是否含表情短码的SQL实现问题

你的问题出在LIKE ':%:'这个匹配模式上——它只会匹配整个字符串以:开头且以:结尾的内容，而不是字符串任意位置存在的表情短码（比如:reaction:、:banana:）。我们需要调整匹配逻辑，准确识别任意位置的:+内容+:格式的短码。

一、基础查询方案（按数据库类型区分） #

1. SQL Server / PostgreSQL #

这两个数据库的LIKE支持否定字符集，可以直接用以下语句：

SELECT 
    CASE 
        WHEN M.Message_Content LIKE '%:[^:]:%' THEN 1 
        ELSE 0 
    END AS Emoji_Used 
FROM [Message] M

或者用PATINDEX（SQL Server专属，更直观）：

SELECT 
    CASE 
        WHEN PATINDEX('%:[^:]:%', M.Message_Content) > 0 THEN 1 
        ELSE 0 
    END AS Emoji_Used 
FROM [Message] M

%:[^:]:%的含义：

• %：匹配任意长度的前置/后置内容（包括空）
• :[^:]：匹配一个:，后面跟着至少一个非冒号字符
• :%：匹配结尾的:和后续任意内容

2. MySQL / MariaDB #

MySQL的LIKE不支持否定字符集，需要用正则表达式匹配：

SELECT 
    CASE 
        WHEN M.Message_Content REGEXP ':[^:]+:' THEN 1 
        ELSE 0 
    END AS Emoji_Used 
FROM Message M

:[^:]+:的含义：匹配:开头，后面跟着一个或多个非冒号字符，最后以:结尾的片段。

二、新增并更新Emoji_Used列的完整方案 #

如果你需要永久添加列并批量更新值，可以执行以下步骤：

SQL Server #

-- 1. 新增Emoji_Used列（BIT类型节省空间，默认0）
ALTER TABLE [Message] ADD Emoji_Used BIT DEFAULT 0;

-- 2. 批量更新列值
UPDATE [Message]
SET Emoji_Used = CASE 
                    WHEN Message_Content LIKE '%:[^:]:%' THEN 1 
                    ELSE 0 
                END;

MySQL / MariaDB #

-- 1. 新增Emoji_Used列（TINYINT类型，默认0）
ALTER TABLE Message ADD Emoji_Used TINYINT(1) DEFAULT 0;

-- 2. 批量更新列值
UPDATE Message
SET Emoji_Used = CASE 
                    WHEN Message_Content REGEXP ':[^:]+:' THEN 1 
                    ELSE 0 
                END;

边界情况说明 #

• 会正确匹配：字符串开头/结尾的表情短码（如:banana: hello、hello :reaction:）、中间的表情短码
• 会排除：空的冒号对（如::），避免误判

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