conn zhang/weizhao Connected. SQL> select * from test12; NAME ----------------------------------------" />
首页 » ORACLE » sql 一组字符串中出现几个不同字符

sql 一组字符串中出现几个不同字符

计算一个字符串类型字段里出现了几个不同的字符,如“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:感谢 温州-名次的网友

打赏

对不起,这篇文章暂时关闭评论。