oracle数据库sql语句 通过分隔符求一个字段里的字符个数

问题描述：

通过分隔符求一个字段里的字符个数，例如，求id为1的type里面省份的个数；

 sql语句如下：

SELECT COUNT(N.VALUE)
  FROM (SELECT M.NAME NAME, COUNT(NAME) VALUE
          FROM (SELECT REGEXP_SUBSTR(TS.type, '[^|]+', 1, L) AS NAME
                  FROM test_columns TS,
                       (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 1000)
                 WHERE L(+) <=
                       LENGTH(TS.type) - LENGTH(REPLACE(TS.type, '|')) + 1
                   and ts.id = '1') M
         WHERE NAME IS NOT NULL
         GROUP BY (M.NAME)
         ORDER BY COUNT(1) DESC) N

结果：

剖析sql语句：

SELECT COUNT(N.VALUE)
  FROM (SELECT M.NAME NAME, COUNT(NAME) VALUE
          FROM (SELECT REGEXP_SUBSTR(TS.type, '[^|]+', 1, L) AS NAME    -->固定函数，REGEXP_SUBSTR() 函数中的 ‘[^|]+’ 是根据你的字段是以什么隔开的；
                  FROM test_columns TS,
                       (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 1000)
                 WHERE L(+) <=
                       LENGTH(TS.type) - LENGTH(REPLACE(TS.type, '|')) + 1   -->WHERE条件中的(+)必须加上、否则 TS.NAME 字段为空的数据无法取得、而且加上(+)后、SQL执行效率能够提高几个数量级
                                                                                但是这里由于我们需要筛选出TS.NAME字段为空的数据，所以后面加了NAME IS NOT NULL

                   and ts.id = '1') M
         WHERE NAME IS NOT NULL
         GROUP BY (M.NAME)
         ORDER BY COUNT(1) DESC) N

 错误做法：