如何统计数据库Text类型字段中子字符串的出现次数？

你的核心问题是忘记将字符长度差除以目标子字符串的长度了！原来的公式LEN(...) - LEN(REPLACE(...))得到的是被替换掉的总字符数，而非子字符串的出现次数，必须除以子串的长度才能得到正确结果。

正确的查询语句 #

针对TEXT字段，我们可以先将其转换为VARCHAR(MAX)（兼容所有字符串函数），再结合完整的次数计算公式：

SELECT 
    NAME,
    TEXT_FIELD,
    -- 计算子字符串出现次数：(总字符数 - 替换后字符数) / 子串长度
    (LEN(CONVERT(VARCHAR(MAX), TEXT_FIELD)) - LEN(REPLACE(CONVERT(VARCHAR(MAX), TEXT_FIELD), 'view', ''))) / LEN('view') AS VIEW_COUNT
FROM TESTING 
WHERE TEXT_FIELD LIKE '%view%';

为什么之前的结果错误？ #

以你的测试数据为例：

• 目标子串view长度为4，每出现一次，替换后会减少4个字符
• NAME1的文本中view实际出现4次（review、view、views、view），总字符差为16，16÷4=4（正确次数）
• NAME2的文本中view实际出现2次（view、review），总字符差为8，8÷4=2（正确次数）
  你之前的查询直接返回了字符差，所以得到了16和8的错误结果。

额外建议 #

如果你的SQL Server版本支持（SQL Server 2005及以上），建议将TEXT字段修改为VARCHAR(MAX)：

ALTER TABLE TESTING ALTER COLUMN TEXT_FIELD VARCHAR(MAX);

TEXT是已废弃的旧数据类型，VARCHAR(MAX)具备相同的大文本存储能力，同时原生支持所有字符串函数，无需额外转换即可直接使用统计公式。

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