如何使用T-SQL脚本获取字符串中大于0的连续数字的计数

我想计算大于0的连续字符串数

例如320244434321:结果为9,因为在0之后是大于1的连续字符串(244434321)

例如320244434:结果为6,因为在0之后是大于1的连续字符串(244434)

例如321244430:结果为8,因为在0之前是大于1的连续字符串(32124443)

例如32012076:结果为2

评论
  • 哎呦喂
    哎呦喂 回复

    Since you have not mentioned RDBMS here is solution in Oracle

    WITH TAB1 AS 
    (select '320244434321' col1 from dual
     union
     select '320244434' col1 from dual
     UNION 
     select '321244430' col1 from dual
     UNION 
     select '32012076' col1 from dual
     ) 
     select col1 , max(LENGTH ( regexp_substr(col1,'[^0]+', 1, level)))  from TAB1
     connect by regexp_substr(col1, '[^0]+', 1, level) is not null
     group by col1  \\
    

    Demo : https://rextester.com/CHUF15464