目錄

• 一、簡(jiǎn)介
• 二、使用說明
• 對(duì)應(yīng)參數(shù)描述
• 我們以一些示例講解使用方法
• 三、instr()與like比較
• instr函數(shù)也有三種情況
• 下面通過一個(gè)示例說明like 與 instr()的使用比較
• 四、效率對(duì)比
• 五、總結(jié)

一、簡(jiǎn)介

相信大家都使用過like進(jìn)行模糊匹配查詢，在oracle中，instr()方法可以用來(lái)代替like進(jìn)行模糊查詢，大數(shù)據(jù)量的時(shí)候效率更高。

本文將對(duì)instr()的基本使用方法進(jìn)行詳解以及通過示例講解與like的效率對(duì)比。

二、使用說明

instr(sourceString,destString,start,appearPosition) ? ??

對(duì)應(yīng)參數(shù)描述

instr（'源字符串' , '目標(biāo)字符串' ,'開始位置','第幾次出現(xiàn)'），返回目標(biāo)字符串在源字符串中的位置。

后面兩個(gè)參數(shù)可要可不要。

我們以一些示例講解使用方法

【a】從開頭開始查找第一個(gè)‘h’出現(xiàn)的位置

--從開頭開始查找第一個(gè)‘h"出現(xiàn)的位置select instr("zhangsan", "h") as idx from dual; --2

查詢結(jié)果：

【b】從開頭開始查找‘an’在字符串中的位置

--從開頭開始查找‘a(chǎn)n"在字符串中的位置select instr("zhangsan","an") idx from dual; --3

查詢結(jié)果：

【c】從第一個(gè)位置開始查找，返回第二次出現(xiàn)‘a’的位置

--從第一個(gè)位置開始查找，返回第二次出現(xiàn)‘a(chǎn)"的位置select instr("zhangsan","a",1,"2") idx from dual; --7

查詢結(jié)果：

【d】從倒數(shù)第一個(gè)位置開始，查找第一次出現(xiàn)‘a’的位置

--從倒數(shù)第一個(gè)位置開始，查找第一次出現(xiàn)‘a(chǎn)"的位置select instr("zhangsan","a",-1,1) idx from dual;  --7

查詢結(jié)果：

【e】從倒數(shù)第一個(gè)位置開始，返回第二次出現(xiàn)‘a’的位置

--從倒數(shù)第一個(gè)位置開始，返回第二次出現(xiàn)‘a(chǎn)"的位置select instr("zhangsan","a",-1,2) idx from dual;   --3

查詢結(jié)果：

三、instr()與like比較

instr函數(shù)也有三種情況

• a. instr(字段,'關(guān)鍵字') > 0 相當(dāng)于 字段like '%關(guān)鍵字%': 表示在字符串中包含‘關(guān)鍵字’
• b. instr(字段,'關(guān)鍵字') = 1 相當(dāng)于 字段like '關(guān)鍵字%' 表示以‘關(guān)鍵字’開頭的字符串
• c. instr(字段,'關(guān)鍵字') = 0 相當(dāng)于 字段not like '%關(guān)鍵字%' 表示在字符串中不包含‘關(guān)鍵字’

下面通過一個(gè)示例說明like 與 instr()的使用比較

【a】使用like進(jìn)行模糊查詢

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "xiaoming" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all select * from temp5) res where res.name like "%zhang%"

查詢字符串中包含‘zhang’的結(jié)果：

【b】使用instr()進(jìn)行模糊查詢

(1) 查詢字符串中包含‘zhang’的結(jié)果：

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "xiaoming" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all  select * from temp5) res where instr(res.name,"zhang") > 0;

(2) 查詢字符串中不包含‘zhang’的結(jié)果：

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "xiaoming" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all select * from temp5) res where instr(res.name,"zhang") = 0;

(3) 查詢以‘zhang’開頭的字符串：

with temp1 as (select "zhangsan" as name from dual),temp2 as (select "zhangsi" as name from dual),temp3 as (select "sizhangsan" as name from dual),temp4 as (select "xiaohong" as name from dual),temp5 as (select "zhaoliu" as name from dual) select * from (select * from temp1 union allselect * from temp2union allselect * from temp3union allselect * from temp4union all  select * from temp5) res where instr(res.name,"zhang") = 1;

(4)instr與like特殊用法

select id, name from users where instr("a, b", id) > 0;--等價(jià)于select id, name  from users where id = a    or id = b;--等價(jià)于select id, name from users where id in (a, b);

四、效率對(duì)比

【a】使用plsql創(chuàng)建一張十萬(wàn)條數(shù)據(jù)測(cè)試數(shù)據(jù)表，同時(shí)為需要進(jìn)行模糊查詢的列增加索引

--創(chuàng)建10萬(wàn)條測(cè)試數(shù)據(jù)create table test_instr_like as select rownum as id,"zhangsan" as name from dualconnect by level <= 100000; --name列建立索引create index idx_tb_name on test_instr_like(name);

【b】使用like進(jìn)行模糊查詢

select * from TEST_INSTR_LIKE t where t.name like "%zhang%"

總耗時(shí)： 60秒

【c】使用instr進(jìn)行模糊查詢

select * from TEST_INSTR_LIKE t where instr(t.name, "zhang") > 0;

總耗時(shí)：50秒

由圖可見，instr查詢的速度確實(shí)比like快一些，但是，看執(zhí)行計(jì)劃的話，instr卻比like耗時(shí)一點(diǎn)。如下圖：

五、總結(jié)

以上是對(duì)instr基本使用方法的講解以及通過示例對(duì)比了like與instr的效率，在進(jìn)行模糊查詢的時(shí)候，能用instr的話就盡量用instr,畢竟數(shù)據(jù)量大的時(shí)候還是有一點(diǎn)優(yōu)勢(shì)的，本文是筆者對(duì)like以及instr的一些總結(jié)和見解，僅供大家學(xué)習(xí)參考，也希望大家多多支持。