SQL找出字串出現次數

1.用CONNECT BY LEVEL
   把字串切割，然後再group by
   再去看你要的字串會出現幾次

select count(*), substr('10', '20', '30', level, 1) from dual connect by level <= length('10', '20', '30')
group by substr('10', '20', '30', level, 1)
having substr('10', '20', '30', level, 1 = ',';

https://forums.oracle.com/forums/thread.jspa?threadID=2358534

2.用replace，然後再去減掉原本的字串

select length(replace('10', '20', '30'), ',', '--')) - length('10', '20', '30') from dual;

http://dbaspot.com/oracle-server/405323-counting-strings.html

第二個方法很厲害，都沒想過可以先replace 再去減掉長度
讚!