按字符分割计算列数

---计算按,分割列数

SQL> select * from v;

STR
----------------
10,CLARK,MANAGER

SQL> select LENGTH(translate(translate(trim(str),'a0123456789','a'),'#ABCDEFGHIJKLMNOPQRSTUVWXYZ','#'))+1  from v;

LENGTH(TRANSLATE(TRANSLATE(TRIM(STR),'A0123456789','A'),'#ABCDEFGHIJKLMNOPQRSTUVWXYZ','#'))+1
---------------------------------------------------------------------------------------------
                                                                                            3

--如果按$#分割:

CREATE OR REPLACE VIEW v AS
SELECT '10$#CLARK$#MANAGER' AS str FROM dual;

SQL> select * from v;

STR
------------------
10$#CLARK$#MANAGER


SQL> select length(translate(translate(trim(str),'a0123456789','a'),'@ABCDEFGHIJKLMNOPQRSTUVWXYZ','@'))/length('$#') +1  from v;

LENGTH(TRANSLATE(TRANSLATE(TRIM(STR),'A0123456789','A'),'@ABCDEFGHIJKLMNOPQRSTUVWXYZ','@'))/LENGTH('$#')+1
----------------------------------------------------------------------------------------------------------
                                                                                                     3