很棒的问题！要统计单词里特定字符的出现次数，不同SQL方言的实现细节略有差异，但核心思路都是利用替换字符+计算长度差的方法——把目标字符从原字符串中全部移除，用原字符串的长度减去替换后的长度，差值就是该字符的出现次数。

针对你的需求：统计字符'L'、'E'、'T'、'R'、'B'、'K'在字符串'LETTERBOOK'中的出现次数，下面是主流数据库的具体示例：

MySQL / MariaDB 实现 #

使用LENGTH()获取字符串长度，配合REPLACE()移除目标字符：

SELECT
  'LETTERBOOK' AS original_string,
  LENGTH('LETTERBOOK') - LENGTH(REPLACE('LETTERBOOK', 'L', '')) AS count_L,
  LENGTH('LETTERBOOK') - LENGTH(REPLACE('LETTERBOOK', 'E', '')) AS count_E,
  LENGTH('LETTERBOOK') - LENGTH(REPLACE('LETTERBOOK', 'T', '')) AS count_T,
  LENGTH('LETTERBOOK') - LENGTH(REPLACE('LETTERBOOK', 'R', '')) AS count_R,
  LENGTH('LETTERBOOK') - LENGTH(REPLACE('LETTERBOOK', 'B', '')) AS count_B,
  LENGTH('LETTERBOOK') - LENGTH(REPLACE('LETTERBOOK', 'K', '')) AS count_K;

执行后返回结果：

PostgreSQL 实现 #

PostgreSQL推荐用CHAR_LENGTH()（按字符数计数，而非字节数）配合REPLACE()：

SELECT
  'LETTERBOOK' AS original_string,
  CHAR_LENGTH('LETTERBOOK') - CHAR_LENGTH(REPLACE('LETTERBOOK', 'L', '')) AS count_L,
  CHAR_LENGTH('LETTERBOOK') - CHAR_LENGTH(REPLACE('LETTERBOOK', 'E', '')) AS count_E,
  CHAR_LENGTH('LETTERBOOK') - CHAR_LENGTH(REPLACE('LETTERBOOK', 'T', '')) AS count_T,
  CHAR_LENGTH('LETTERBOOK') - CHAR_LENGTH(REPLACE('LETTERBOOK', 'R', '')) AS count_R,
  CHAR_LENGTH('LETTERBOOK') - CHAR_LENGTH(REPLACE('LETTERBOOK', 'B', '')) AS count_B,
  CHAR_LENGTH('LETTERBOOK') - CHAR_LENGTH(REPLACE('LETTERBOOK', 'K', '')) AS count_K;

结果与MySQL示例完全一致。

SQL Server 实现 #

使用LEN()获取字符串长度，搭配REPLACE()即可：

SELECT
  'LETTERBOOK' AS original_string,
  LEN('LETTERBOOK') - LEN(REPLACE('LETTERBOOK', 'L', '')) AS count_L,
  LEN('LETTERBOOK') - LEN(REPLACE('LETTERBOOK', 'E', '')) AS count_E,
  LEN('LETTERBOOK') - LEN(REPLACE('LETTERBOOK', 'T', '')) AS count_T,
  LEN('LETTERBOOK') - LEN(REPLACE('LETTERBOOK', 'R', '')) AS count_R,
  LEN('LETTERBOOK') - LEN(REPLACE('LETTERBOOK', 'B', '')) AS count_B,
  LEN('LETTERBOOK') - LEN(REPLACE('LETTERBOOK', 'K', '')) AS count_K;

同样能得到正确的计数结果。

关键注意事项 #

• 大小写敏感：默认情况下这些函数是区分大小写的，如果需要统计不区分大小写的次数，可以先统一转换字符串和目标字符的大小写，比如REPLACE(UPPER('LetterBook'), 'L', '')。
• 多字节字符处理：如果处理中文等多字节字符，务必使用按字符计数的长度函数（如PostgreSQL的CHAR_LENGTH()、MySQL的CHAR_LENGTH()），避免用按字节计数的LENGTH()导致结果错误。

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