SQLite区分大小写通配符查询失效问题求助

你需要区分大小写匹配独立的'ABLI'字符串，排除大小写混合、嵌入其他单词的情况，以下是可行的解决方法：

优先方案：使用REGEXP（推荐） #

SQLite的REGEXP支持正则表达式，只要启用了正则扩展（比如PCRE），就能精准匹配独立单词：

SELECT title
FROM INVENTORY_TEST
WHERE title REGEXP '^ABLI([^A-Za-z]|$)|[^A-Za-z]ABLI[^A-Za-z]|([^A-Za-z]|^)ABLI$'
COLLATE BINARY;

规则说明： #

• COLLATE BINARY：强制区分大小写，避免列默认排序规则导致的不区分大小写匹配
• 正则分为三部分，覆盖所有独立出现的场景：
  1. ^ABLI([^A-Za-z]|$)：匹配开头就是ABLI的情况（比如ABLI xxx、ABLI）
  2. [^A-Za-z]ABLI[^A-Za-z]：匹配ABLI在字符串中间，前后都是非字母的情况（比如xxx ABLI yyy、xxx-ABLI-yyy）
  3. ([^A-Za-z]|^)ABLI$：匹配结尾是ABLI的情况（比如xxx ABLI）

备选方案：纯LIKE实现（无需REGEXP扩展） #

如果无法启用REGEXP，可以用多个LIKE条件组合实现需求：

SELECT title
FROM INVENTORY_TEST
WHERE 
  -- 开头是ABLI，后续非字母或结尾
  (title LIKE 'ABLI%' AND (LENGTH(title) = 4 OR SUBSTR(title, 5, 1) NOT BETWEEN 'A' AND 'Z' AND SUBSTR(title, 5, 1) NOT BETWEEN 'a' AND 'z'))
  -- 中间包含ABLI，前后都是非字母
  OR (title LIKE '%ABLI%' AND INSTR(title, 'ABLI') > 1 AND INSTR(title, 'ABLI') + 4 <= LENGTH(title) 
      AND SUBSTR(title, INSTR(title, 'ABLI') - 1, 1) NOT BETWEEN 'A' AND 'Z' AND SUBSTR(title, INSTR(title, 'ABLI') - 1, 1) NOT BETWEEN 'a' AND 'z'
      AND SUBSTR(title, INSTR(title, 'ABLI') + 4, 1) NOT BETWEEN 'A' AND 'Z' AND SUBSTR(title, INSTR(title, 'ABLI') + 4, 1) NOT BETWEEN 'a' AND 'z')
  -- 结尾是ABLI，前面非字母或开头
  OR (title LIKE '%ABLI' AND (LENGTH(title) = 4 OR SUBSTR(title, LENGTH(title) - 4, 1) NOT BETWEEN 'A' AND 'Z' AND SUBSTR(title, LENGTH(title) - 4, 1) NOT BETWEEN 'a' AND 'z'))
COLLATE BINARY;

你之前尝试的问题分析 #

• UPPER(title) LIKE '%ABLI%'/title LIKE '%ABLI%' COLLATE NOCASE：转大写或启用NOCASE会匹配所有大小写混合的abli变体，所以返回全部21行
• title LIKE '%ABLI%'：如果列是NOCASE排序规则，同样不区分大小写；如果是BINARY，会匹配嵌入在单词里的ABLI（比如estABLIshment），不符合需求
• title REGEXP '\bABLI\b'：部分SQLite正则扩展的\b仅识别空格作为单词边界，会漏掉开头/结尾的ABLI条目
• title LIKE '%[^A-Za-z]ABLI[^A-Za-z]%'：LIKE不支持[^...]这类正则字符类语法，所以条件无效返回0行

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