如何用SQL正则函数区分Emoji与其他非ASCII字符？

假设你的表结构如下：

你之前用的REGEXP_SUBSTR(Comment_Text, '[^\x00-\x7F]+', 1, 1)会把所有非ASCII字符（包括π、Þ这类）都匹配出来，要精准识别Emoji，得用对应Unicode区间的正则表达式，以下是主流SQL方言的实现方案：

1. MySQL 实现 #

利用REGEXP_SUBSTR提取首个Emoji，REGEXP_LIKE生成二进制标识列：

SELECT 
  CommentID,
  Comment_Text,
  -- 提取第一个Emoji（支持组合型Emoji）
  REGEXP_SUBSTR(Comment_Text, 
    '[\\x{1F600}-\\x{1F64F}\\x{1F300}-\\x{1F5FF}\\x{1F680}-\\x{1F6FF}\\x{1F1E0}-\\x{1F1FF}\\x{2600}-\\x{26FF}\\x{2700}-\\x{27BF}\\x{1F900}-\\x{1F9FF}\\x{1F004}-\\x{1F0CF}\\x{1F000}-\\x{1F02F}](?:\\x{200D}[\\x{1F600}-\\x{1F64F}\\x{1F300}-\\x{1F5FF}\\x{1F680}-\\x{1F6FF}\\x{1F1E0}-\\x{1F1FF}\\x{2600}-\\x{26FF}\\x{2700}-\\x{27BF}\\x{1F900}-\\x{1F9FF}\\x{1F004}-\\x{1F0CF}\\x{1F000}-\\x{1F02F}])*|\\x{1F3FB}-\\x{1F3FF}',
    1, 1) AS Extracted_Emoji,
  -- 生成二进制标识：1=含Emoji，0=不含
  CASE WHEN REGEXP_LIKE(Comment_Text, 
    '[\\x{1F600}-\\x{1F64F}\\x{1F300}-\\x{1F5FF}\\x{1F680}-\\x{1F6FF}\\x{1F1E0}-\\x{1F1FF}\\x{2600}-\\x{26FF}\\x{2700}-\\x{27BF}\\x{1F900}-\\x{1F9FF}\\x{1F004}-\\x{1F0CF}\\x{1F000}-\\x{1F02F}](?:\\x{200D}[\\x{1F600}-\\x{1F64F}\\x{1F300}-\\x{1F5FF}\\x{1F680}-\\x{1F6FF}\\x{1F1E0}-\\x{1F1FF}\\x{2600}-\\x{26FF}\\x{2700}-\\x{27BF}\\x{1F900}-\\x{1F9FF}\\x{1F004}-\\x{1F0CF}\\x{1F000}-\\x{1F02F}])*|\\x{1F3FB}-\\x{1F3FF}')
  THEN 1 ELSE 0 END AS Has_Emoji
FROM your_table;

2. PostgreSQL 实现 #

使用regexp_match提取Emoji，~操作符判断是否包含Emoji：

SELECT 
  CommentID,
  Comment_Text,
  -- 提取第一个Emoji（支持组合型）
  (SELECT regexp_match(Comment_Text, 
    '([\x{1F600}-\x{1F64F}\x{1F300}-\x{1F5FF}\x{1F680}-\x{1F6FF}\x{1F1E0}-\x{1F1FF}\x{2600}-\x{26FF}\x{2700}-\x{27BF}\x{1F900}-\x{1F9FF}\x{1F004}-\x{1F0CF}\x{1F000}-\x{1F02F}](?:\x{200D}[\x{1F600}-\x{1F64F}\x{1F300}-\x{1F5FF}\x{1F680}-\x{1F6FF}\x{1F1E0}-\x{1F1FF}\x{2600}-\x{26FF}\x{2700}-\x{27BF}\x{1F900}-\x{1F9FF}\x{1F004}-\x{1F0CF}\x{1F000}-\x{1F02F}])*|\x{1F3FB}-\x{1F3FF}'))[1] AS Extracted_Emoji,
  -- 二进制标识列
  CASE WHEN Comment_Text ~ '[\x{1F600}-\x{1F64F}\x{1F300}-\x{1F5FF}\x{1F680}-\x{1F6FF}\x{1F1E0}-\x{1F1FF}\x{2600}-\x{26FF}\x{2700}-\x{27BF}\x{1F900}-\x{1F9FF}\x{1F004}-\x{1F0CF}\x{1F000}-\x{1F02F}](?:\x{200D}[\x{1F600}-\x{1F64F}\x{1F300}-\x{1F5FF}\x{1F680}-\x{1F6FF}\x{1F1E0}-\x{1F1FF}\x{2600}-\x{26FF}\x{2700}-\x{27BF}\x{1F900}-\x{1F9FF}\x{1F004}-\x{1F0CF}\x{1F000}-\x{1F02F}])*|\x{1F3FB}-\x{1F3FF}'
  THEN 1 ELSE 0 END AS Has_Emoji
FROM your_table;

关键说明 #

• 上述正则覆盖了所有标准Emoji的Unicode区间，不会匹配π（U+03C0）、Þ（U+00DE）这类非ASCII普通字符
• 正则中的\x{200D}是零宽连接符，用来匹配组合型Emoji（比如👨🏿‍💻这类由多个码点组成的表情）
• \x{1F3FB}-\x{1F3FF}是肤色修饰符，确保带肤色的Emoji能被正确识别

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