ORACLE计算字符在字符串中出现的次数

SQL> create table t1(describe varchar2(20));

Table created.

SQL> insert into t1 values('hello world');

1 row created.

SQL> insert into t1 values('hello everyone');

1 row created.

SQL> insert into t1 values('hello all');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

DESCRIBE
--------------------
hello world
hello everyone
hello all

SQL> select
  2  (length(describe)-length(replace(describe,'h','')))/length('h') as cnt
  3  from t1;

       CNT
----------
         1
         1
         1

SQL> select
  2  (length(describe)-length(replace(describe,'l','')))/length('l') as cnt
  3  from t1;

       CNT
----------
         3
         2
         4

也许你会觉得后面那个除数length()函数是没有用的，的确，在这个例子中是没用的，不过当被查找的字符串长度不为1时，我们再来看看会有什么结果。

SQL> insert into t1 values('hohohoh');

1 row created.

SQL> select
  2  (length(describe)-length(replace(describe,'ho','')))/length('ho') as cnt
  3  from t1;

       CNT
----------
         0
         0
         0
         3

SQL> select
  2  length(describe)-length(replace(describe,'ho','')) as cnt
  3  from t1;

       CNT
----------
         0
         0
         0
         6       --这个结果是错误的 因为没有除以被查找字符串的长度

SQL>