ANBOB™

计算一个字符串类型字段里出现了几个不同的字符，如“abcaaaab”=3 ,”aaaaaaa”=1

操作如下

SQL> conn zhang/weizhao
Connected.
SQL> select * from test12;

NAME
—————————————-
aabbca
aada
aadacc
aadaceec

sql>CREATE OR REPLACE FUNCTION length_distinct (col VARCHAR2)
RETURN INT
IS
c_count   INT;
BEGIN
SELECT COUNT (*)
INTO c_count
FROM (SELECT DISTINCT REGEXP_SUBSTR (col, ‘.’, ROWNUM, 1)
FROM DUAL
CONNECT BY ROWNUM < LENGTH (col));

RETURN c_count;
END;

SQL> select name, length_distinct(name) c_count from test12;

NAME                                        C_COUNT
—————————————- ———-
aabbca                                            3
aada                                              2
aadacc                                            3
aadaceec                                          4

ps:感谢 温州-名次的网友

打赏

微信扫一扫，打赏作者吧～