SQL> WITH tt AS 2 (SELECT '1,8,11,17,20,28,31,34,75,4,44,47,57,14,26,38,53,60,63,65,68' num 3 FROM dual) 4 SELECT COUNT(*) 5 FROM (SELECT regexp_substr(num, '[^,]+', 1, LEVEL) num 6 FROM tt 7 CONNECT BY LEVEL <= length(num) - length(REPLACE(num, ','))) 8 WHERE num < 50;
COUNT(*) ---------- 14
SQL>
--上面少了一项 SQL> WITH tt AS 2 (SELECT '1,8,11,17,20,28,31,34,75,4,44,47,57,14,26,38,53,60,63,65,68' num 3 FROM dual) 4 SELECT COUNT(*) 5 FROM (SELECT regexp_substr(num, '[^,]+', 1, LEVEL) num 6 FROM tt 7 CONNECT BY LEVEL <= length(num) - length(REPLACE(num, ',')) + 1) 8 WHERE num < 50;
2 (SELECT '1,8,11,17,20,28,31,34,75,4,44,47,57,14,26,38,53,60,63,65,68' num
3 FROM dual)
4 SELECT COUNT(*)
5 FROM (SELECT regexp_substr(num, '[^,]+', 1, LEVEL) num
6 FROM tt
7 CONNECT BY LEVEL <= length(num) - length(REPLACE(num, ',')))
8 WHERE num < 50;
COUNT(*)
----------
14
SQL>
SQL> WITH tt AS
2 (SELECT '1,8,11,17,20,28,31,34,75,4,44,47,57,14,26,38,53,60,63,65,68' num
3 FROM dual)
4 SELECT COUNT(*)
5 FROM (SELECT regexp_substr(num, '[^,]+', 1, LEVEL) num
6 FROM tt
7 CONNECT BY LEVEL <= length(num) - length(REPLACE(num, ',')) + 1)
8 WHERE num < 50;
COUNT(*)
----------
14
SQL>