如何修改SQL表达式动态统计字符串首字符重复次数（长度<10）

我有带重复字符的字符串数据，比如'aaaaaa'、'bbbb'、'cccdef'，想要的统计结果是：aaaaaa: 6、bbbb: 4、cccdef: 3。具体需求：

• 动态统计字符串左侧第一个字符的重复次数
• 仅适用于字符串最大长度小于10的场景

现有参考表达式：

SELECT LENGTH(col) - LENGTH(REGEXP_REPLACE(col, ',', '')) FROM TableName

我尝试了下面的代码但报错，请问怎么修改参考表达式实现需求？
报错代码：

LENGTH(Client_Comment1_CPS_OTHR) - (LENGTH(REGEXP_REPLACE(Client_Comment1_CPS_OTHR, (CONCAT("'",   LEFT(Client_Comment1_CPS_OTHR,1),"'"), '')) as repeatedchar_cnt

解决方案 #

你的报错源于两处问题：一是括号不匹配导致语法错误，二是给正则模式额外加了单引号，不符合REGEXP_REPLACE的参数要求。这里提供两种可行的修改方案：

方案1：基于原参考表达式改造 #

SELECT 
    Client_Comment1_CPS_OTHR,
    LENGTH(Client_Comment1_CPS_OTHR) - LENGTH(REGEXP_REPLACE(Client_Comment1_CPS_OTHR, CONCAT('^', LEFT(Client_Comment1_CPS_OTHR,1)), '')) AS repeatedchar_cnt
FROM TableName;

方案2：更直观的正则提取法 #

直接提取开头连续的首字符，再获取其长度：

SELECT 
    Client_Comment1_CPS_OTHR,
    LENGTH(REGEXP_SUBSTR(Client_Comment1_CPS_OTHR, CONCAT('^', LEFT(Client_Comment1_CPS_OTHR,1), '+'))) AS repeatedchar_cnt
FROM TableName;

关键说明 #

• ^：正则符号，匹配字符串起始位置，确保只统计开头的重复字符
• CONCAT('^', 首字符, '+')：拼接出匹配开头连续首字符的正则模式，+表示匹配1个或多个相同字符
• 若你的SQL方言支持||字符串拼接，可替换CONCAT，写法更简洁：'^' || LEFT(Client_Comment1_CPS_OTHR,1) || '+'

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