SQL> with T AS( 2 SELECT 'A1' A FROM DUAL UNION ALL 3 SELECT 'A11' FROM DUAL UNION ALL 4 SELECT 'A111' FROM DUAL UNION ALL 5 SELECT 'A1112' FROM DUAL UNION ALL 6 SELECT 'A2222' FROM DUAL 7 ) 8 SELECT A,REGEXP_COUNT(WMSYS.WM_CONCAT(A)OVER(),A||'[^,]*') B FROM T;A B ----- ---------- A1 4 A11 3 A111 2 A1112 1 A2222 1SQL>
2 SELECT 'A1' A FROM DUAL UNION ALL
3 SELECT 'A11' FROM DUAL UNION ALL
4 SELECT 'A111' FROM DUAL UNION ALL
5 SELECT 'A1112' FROM DUAL UNION ALL
6 SELECT 'A2222' FROM DUAL
7 )
8 SELECT A,REGEXP_COUNT(WMSYS.WM_CONCAT(A)OVER(),A||'[^,]*') B FROM T;A B
----- ----------
A1 4
A11 3
A111 2
A1112 1
A2222 1SQL>