oracle字符数量,sql – 计算Oracle中最大重复字符的数量

一个奇怪的要求,但这是一种方式：

create or replace

function max_repetetive_letter_count (string varchar2) return integer

is

letter_col SYS.KU$_VCNT := SYS.KU$_VCNT(); -- A handy collection type

l_max_count integer;

begin

letter_col.extend(length(string));

for i in 1..length(string) loop

letter_col(i) := substr(string,i,1);

end loop;

select max(letter_count)

into l_max_count

from

( select column_value, count(*) letter_count

from table(letter_col)

group by column_value

);

return l_max_count;

end;

/

用法示例：

SQL> select string, max_repetetive_letter_count(string)

2 from

3 ( select 'ajkhsdjjjaajjj' as string from dual

4 union all

5 select 'AABBDDDDDDD' as string from dual

6 );

STRING MAX_REPETETIVE_LETTER_COUNT(STRING)

-------------- -----------------------------------

ajkhsdjjjaajjj 7

AABBDDDDDDD 7

(注意你的例子中的6是不正确的！)