ORACLE中SQL返回字符串中大写字母第二次出现位置

通过正则表达式
1、 SELECT substr(t.name,1,(REGEXP_INSTR (t.name,’[A-Z]’, 1,2)-1))||’ ‘||substr(t.name,REGEXP_INSTR (t.name,’[A-Z]’, 1,2)) FROM css t;
通过ASCII码
2、select case when ASCII(substr(t.name, 3)) between 65 and 90
then substr(t.name,1,2)||’ ‘||substr(t.name,3)
when ASCII(substr(t.name, 4)) between 65 and 90
then substr(t.name,1,3)||’ ‘||substr(t.name,4)
when ASCII(substr(t.name, 5)) between 65 and 90
then substr(t.name,1,4)||’ ‘||substr(t.name,5)
when ASCII(substr(t.name, 6)) between 65 and 90
then substr(t.name,1,5)||’ ‘||substr(t.name,6)
when ASCII(substr(t.name, 7)) between 65 and 90
then substr(t.name,1,6)||’ ‘||substr(t.name,7)
when ASCII(substr(t.name, 8)) between 65 and 90
then substr(t.name,1,7)||’ ‘||substr(t.name,8)
when ASCII(substr(t.name, 9)) between 65 and 90
then substr(t.name,1,8)||’ ‘||substr(t.name,9)
when ASCII(substr(t.name, 10)) between 65 and 90
then substr(t.name,1,9)||’ ‘||substr(t.name,10)
when ASCII(substr(t.name, 11)) between 65 and 90
then substr(t.name,1,10)||’ ‘||substr(t.name,11)
when ASCII(substr(t.name, 12)) between 65 and 90
then substr(t.name,1,11)||’ ‘||substr(t.name,12)
when ASCII(substr(t.name, 13)) between 65 and 90
then substr(t.name,1,12)||’ ‘||substr(t.name,13)
when ASCII(substr(t.name, 14)) between 65 and 90
then substr(t.name,1,13)||’ ‘||substr(t.name,14)
when ASCII(substr(t.name, 15)) between 65 and 90
then substr(t.name,1,14)||’ ‘||substr(t.name,15)
when ASCII(substr(t.name, 16)) between 65 and 90
then substr(t.name,1,15)||’ ‘||substr(t.name,15)
when ASCII(substr(t.name, 17)) between 65 and 90
then substr(t.name,1,16)||’ ‘||substr(t.name,17)
when ASCII(substr(t.name, 18)) between 65 and 90
then substr(t.name,1,17)||’ ‘||substr(t.name,18)
when ASCII(substr(t.name, 19)) between 65 and 90
then substr(t.name,1,18)||’ ‘||substr(t.name,19)
when ASCII(substr(t.name, 20)) between 65 and 90
then substr(t.name,1,19)||’ ‘||substr(t.name,20)
when ASCII(substr(t.name, 21)) between 65 and 90
then substr(t.name,1,20)||’ ‘||substr(t.name,21)
when ASCII(substr(t.name, 22)) between 65 and 90
then substr(t.name,1,21)||’ ‘||substr(t.name,22)
when ASCII(substr(t.name, 23)) between 65 and 90
then substr(t.name,1,22)||’ ‘||substr(t.name,23)
when ASCII(substr(t.name, 24)) between 65 and 90
then substr(t.name,1,23)||’ ‘||substr(t.name,24)
when ASCII(substr(t.name, 25)) between 65 and 90
then substr(t.name,1,24)||’ ‘||substr(t.name,25)
when ASCII(substr(t.name, 26)) between 65 and 90
then substr(t.name,1,25)||’ ‘||substr(t.name,26)
when ASCII(substr(t.name, 27)) between 65 and 90
then substr(t.name,1,26)||’ ‘||substr(t.name,27)
when ASCII(substr(t.name, 28)) between 65 and 90
then substr(t.name,1,27)||’ ‘||substr(t.name,28)
when ASCII(substr(t.name, 29)) between 65 and 90
then substr(t.name,1,28)||’ ‘||substr(t.name,29)
when ASCII(substr(t.name, 30)) between 65 and 90
then substr(t.name,1,29)||’ ‘||substr(t.name,30)
when ASCII(substr(t.name, 31)) between 65 and 90
then substr(t.name,1,30)||’ ‘||substr(t.name,31)
when ASCII(substr(t.name, 32)) between 65 and 90
then substr(t.name,1,31)||’ ‘||substr(t.name,32)
when ASCII(substr(t.name, 33)) between 65 and 90
then substr(t.name,1,32)||’ ‘||substr(t.name,33)
when ASCII(substr(t.name, 34)) between 65 and 90
then substr(t.name,1,33)||’ ‘||substr(t.name,34)
when ASCII(substr(t.name, 35)) between 65 and 90
then substr(t.name,1,34)||’ ‘||substr(t.name,35)
when ASCII(substr(t.name, 36)) between 65 and 90
then substr(t.name,1,35)||’ ‘||substr(t.name,36)
when ASCII(substr(t.name, 37)) between 65 and 90
then substr(t.name,1,36)||’ ‘||substr(t.name,37)
when ASCII(substr(t.name, 38)) between 65 and 90
then substr(t.name,1,37)||’ ‘||substr(t.name,38)
when ASCII(substr(t.name, 39)) between 65 and 90
then substr(t.name,1,38)||’ ‘||substr(t.name,39)
when ASCII(substr(t.name, 40)) between 65 and 90
then substr(t.name,1,39)||’ ‘||substr(t.name,40)
when ASCII(substr(t.name, 41)) between 65 and 90
then substr(t.name,1,40)||’ ‘||substr(t.name,41)
when ASCII(substr(t.name, 42)) between 65 and 90
then substr(t.name,1,41)||’ ‘||substr(t.name,42)
when ASCII(substr(t.name, 43)) between 65 and 90
then substr(t.name,1,42)||’ ‘||substr(t.name,43)
when ASCII(substr(t.name, 44)) between 65 and 90
then substr(t.name,1,43)||’ ‘||substr(t.name,44)
when ASCII(substr(t.name, 45)) between 65 and 90
then substr(t.name,1,44)||’ ‘||substr(t.name,45)
when ASCII(substr(t.name, 46)) between 65 and 90
then substr(t.name,1,45)||’ ‘||substr(t.name,46)
when ASCII(substr(t.name, 47)) between 65 and 90
then substr(t.name,1,46)||’ ‘||substr(t.name,47)
when ASCII(substr(t.name, 48)) between 65 and 90
then substr(t.name,1,47)||’ '||substr(t.name,48)
else t.name end
from css t;