按字符分割计算列数

来源：互联网发布：斗破苍穹网络剧在哪播编辑：程序博客网时间：2024/04/27 15:43

---计算按,分割列数SQL> select * from v;STR----------------10,CLARK,MANAGERSQL> 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 ASSELECT '10$#CLARK$#MANAGER' AS str FROM dual;SQL> select * from v;STR------------------10$#CLARK$#MANAGERSQL> select length(translate(translate(trim(str),'a0123456789','a'),'@ABCDEFGHIJKLMNOPQRSTUVWXYZ','@'))/length('$#') +1  from v;LENGTH(TRANSLATE(TRANSLATE(TRIM(STR),'A0123456789','A'),'@ABCDEFGHIJKLMNOPQRSTUVWXYZ','@'))/LENGTH('$#')+1----------------------------------------------------------------------------------------------------------                                                                                                     3

0 0