SQL中使用CASE语句结合LIKE匹配方括号内关键词的问题求助

问题背景 #

需要从表中字符串的方括号内提取特定关键词生成新列，示例需求：

• 当Title为Ad - LA - [Ke] [NC] [W] Test for IPHIndi时，输出W
• 当Title为[MWeb] My Ads Center (Batch 3) Part 2时，输出MWeb

原使用的CASE语句存在误匹配问题：比如只要字符串包含字母M就会被标记为M，原因是LIKE是全局模糊匹配，没有限定关键词必须在方括号内。原语句如下：

CASE 
    WHEN title LIKE '%W+MWeb%' THEN 'w+m'
    WHEN title LIKE '%MWeb%' THEN 'MWeb'
    WHEN title LIKE '%W%' THEN 'w'
    WHEN title LIKE '%M%' THEN 'M'
    ELSE 'OTHER'
END AS MARKER

解决方案 #

核心思路是只匹配方括号包裹的关键词，避免全局模糊匹配的误判，以下是主流SQL方言的实现方式：

MySQL/MariaDB 实现 #

使用REGEXP_SUBSTR函数精准匹配方括号内的内容：

CASE
    -- 同时匹配[W]和[MWeb]的场景
    WHEN REGEXP_SUBSTR(title, '\\[(W)\\]') IS NOT NULL AND REGEXP_SUBSTR(title, '\\[MWeb\\]') IS NOT NULL THEN 'w+m'
    -- 匹配方括号内的MWeb
    WHEN REGEXP_SUBSTR(title, '\\[MWeb\\]') IS NOT NULL THEN 'MWeb'
    -- 匹配方括号内的W
    WHEN REGEXP_SUBSTR(title, '\\[W\\]') IS NOT NULL THEN 'w'
    -- 匹配方括号内的M
    WHEN REGEXP_SUBSTR(title, '\\[M\\]') IS NOT NULL THEN 'M'
    ELSE 'OTHER'
END AS MARKER

正则表达式说明：\\[和\\]用于匹配实际的方括号（避免被正则语法解析），中间的内容就是要匹配的目标关键词。

PostgreSQL 实现 #

使用正则匹配操作符~*（不区分大小写）实现精准匹配：

CASE
    WHEN title ~* '\[W\]' AND title ~* '\[MWeb\]' THEN 'w+m'
    WHEN title ~* '\[MWeb\]' THEN 'MWeb'
    WHEN title ~* '\[W\]' THEN 'w'
    WHEN title ~* '\[M\]' THEN 'M'
    ELSE 'OTHER'
END AS MARKER

SQL Server 实现 #

在LIKE中通过转义字符限定方括号匹配：

CASE
    WHEN title LIKE '%\[W\]%' AND title LIKE '%\[MWeb\]%' THEN 'w+m'
    WHEN title LIKE '%\[MWeb\]%' THEN 'MWeb'
    WHEN title LIKE '%\[W\]%' THEN 'w'
    WHEN title LIKE '%\[M\]%' THEN 'M'
    ELSE 'OTHER'
END AS MARKER

注：SQL Server中也可使用LIKE '%[[]MWeb]%'的写法直接匹配方括号。

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