關於字串匹配查詢的總結

判斷一個字元型欄位中出現某個字元超過3次的資料行，如果為了簡單達到目的，可以直接使用Like來做，
SQL> select content from clob_test where content like '%is%is%is%';
CONTENT
--------------------------------------------------------------------------------
this is a test,and it is very useful

但是可能在實際應用中，如果有一些有些特別的需求，比如判斷某個字串出現的次數20次以上的。用Like就有些體力活了。
如果欄位型別是clob,可以使用dbms_lob.instr來實現。
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 PATTERN                        RAW                     IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT


下面來做一個簡單的測試。
SQL> create table clob_test(content clob);
Table created.
SQL> insert into clob_test values('this is a test,and it is very useful');
1 row created.
SQL> insert into clob_test values('here it is');
1 row created.
SQL> commit;
Commit complete.

從中查詢出現is超過3次的資料行。
SQL> select content from clob_test where dbms_lob.instr(content,'is',1,3)>0;
CONTENT
--------------------------------------------------------------------------------
this is a test,and it is very useful

如果是varchar2型別，貌似只能使用like了。其實不然。
如果在10g版本內，對於字串想自己寫一個類似的函式來處理，可以類似下面的形式。
SQL> select content from clob_test where (length(content)-length(replace(content,'is',null)))/(length('is'))>=3;
CONTENT
--------------------------------------------------------------------------------
this is a test,and it is very useful


如果在這個基礎上想更進一步，可以使用11g的regexp_count來實現。
SQL> select content from clob_test where regexp_count(content,'is')>=3;
CONTENT
--------------------------------------------------------------------------------
this is a test,and it is very useful

從上面的例子，可以看出這個函式有多實用，省去了很多其他的處理。
當然了在11g版本中，還有regexp_substr,regexp_instr,regexp_like等等的函式，也很實用。

加一個欄位，在varchar2上作這個測試。
SQL> alter table clob_test add(content2 varchar2(1000));
Table altered.

SQL> insert into clob_test(content2) values('stringtest=100#stringtest=50');
1 row created.

SQL>  insert into clob_test(content2) values('stringtest=200#stringtest=60');
1 row created.

現在是想擷取 串"stringtest=100#stringtest=50"中間的100
如果按照一般的思路，可以這樣來做。
select 
TO_NUMBER (
          SUBSTR (
             content2,
             INSTR (content2,
                    'stringtest=')
             + 11,
             INSTR (
                SUBSTR (
                   content2,
                   INSTR (content2,
                          'stringtest=')
                   + 11),
                '#')
             - 1))content3
from clob_test where content2 is not null;
 
CONTENT3
----------
       100
       200

如果使用regexp_substr來的話，可能一行就可以了。
SQL> select
  2  to_number(replace(regexp_substr(content2,'[^stringtest=]+',1,1) ,'#','')) context3 from clob_test where content2 is not null;
   CONTEXT3
----------
       100
       200