Oracle中instr函數(shù)與substr函數(shù)及自制分割函數(shù)詳解
Oracle instr函數(shù)與substr函數(shù)以及自制分割函數(shù)
instr通常被用來作為判斷某個字符串中是否含有執(zhí)行字符串和將返回結(jié)果作為一些數(shù)據(jù)分割的數(shù)據(jù),即有模糊查詢like的作用,當(dāng)返回的查詢結(jié)果的序號為0的時候就是沒有匹配上
substr函數(shù)就是很簡單明了,就是個截取字符函數(shù)
instr函數(shù)
通常使用instr函數(shù)有三種格式
- instr(字符串,要匹配的字符串)
- instr(字符串,要匹配的字符串,起始位置)
- instr(字符串,要匹配的字符串,起始位置,匹配次序)
實質(zhì)上都是instr(字符串,要匹配的字符串,起始位置,匹配次序),只不過前兩個參數(shù)一定要有數(shù)據(jù),后兩個參數(shù)如果不填就默認(rèn)為1,無論是從前查還是從后查,執(zhí)行的字符串順序是不變的
示例
--在ababc中查找a,只有兩個參數(shù)的時候,起始位置和匹配次序默認(rèn)為1,即從位置1開始,查詢第一次匹配的位置,即結(jié)果為1 select instr('ababc','a') from dual ; --在ababc中查找a,只有三個參數(shù)的時候,匹配次序默認(rèn)為1,起始位置填寫2的話,即從位置2開始,查詢第一次匹配的位置,即結(jié)果為3 select instr('ababc','a',2) from dual ; --在ababc中查找a,有四個參數(shù)的時候,起始位置填寫1,匹配次序填寫2,即從位置1開始,查詢第二次匹配的位置,即結(jié)果為3 select instr('ababc','a',1,2) from dual ; --在ababc中查找a,有四個參數(shù)的時候,起始位置填寫2,匹配次序填寫2,即從位置2開始,查詢第二次匹配的位置,查詢不到結(jié)果,即結(jié)果為0 select instr('ababc','a',2,2) from dual ; --在ababc中查找a,有四個參數(shù)的時候,起始位置填寫-1,匹配次序填寫1,即從位置5(-1就是從后往前查詢)開始,查詢第一次匹配的位置,即結(jié)果為3 select instr('ababc','a',-1,1) from dual ; --在ababc中查找ab,只有兩個參數(shù)的時候,起始位置和匹配次序默認(rèn)為1,即從位置1開始,查詢第一次匹配的a(ab的開頭是a)位置,即結(jié)果為1 select instr('ababc','ab') from dual ; --在ababc中查找ab,只有三個參數(shù)的時候,匹配次序默認(rèn)為1,起始位置填寫2的話,即從位置2開始,查詢第一次匹配的a(ab的開頭是a)位置,即結(jié)果為3 select instr('ababc','ab',2) from dual ; --在ababc中查找ab,有四個參數(shù)的時候,起始位置填寫1,匹配次序填寫2,即從位置1開始,查詢第二次匹配的a(ab的開頭是a)位置,即結(jié)果為3 select instr('ababc','ab',1,2) from dual ; --在ababc中查找ab,有四個參數(shù)的時候,起始位置填寫2,匹配次序填寫2,即從位置2開始,查詢第二次匹配的a(ab的開頭是a)位置,查詢不到結(jié)果,即結(jié)果為0 select instr('ababc','ab',2,2) from dual ; --在ababc中查找ab,有四個參數(shù)的時候,起始位置填寫-1,匹配次序填寫1,即從位置5(-1就是從后往前查詢)開始,查詢第一次匹配的a(ab的開頭是a)位置,即結(jié)果為3 select instr('ababc','ab',-1,1) from dual ; --在ababc中查找ba,有四個參數(shù)的時候,起始位置填寫-1,匹配次序填寫1,即從位置5(-1就是從后往前查詢)開始,查詢第一次匹配的b(ba的開頭是b)位置,即結(jié)果為1 select instr('ababc','ba',-1,1) from dual
可以根據(jù)instr函數(shù)做很多事情
比如有時候會有一些日期臟數(shù)據(jù),格式很混亂,則就可以直接使用instr去匹配進(jìn)行處理
select substr(to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),0,instr(to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),':')-3) from dual
還有l(wèi)ike的作用,下面兩個語句結(jié)果是相同的
select * from (select 'aa' as aa from dual) t where t.aa like '%a%' ; select * from (select 'aa' as aa from dual) t where instr(t.aa,'a')>0
substr函數(shù)
通常使用substr函數(shù)有三種格式
- substr(字符串,起始位置)
- substr(字符串,起始位置,截取字符數(shù))
實質(zhì)上都是substr字符串,起始位置,截取字符數(shù)),只不過前兩個參數(shù)一定要有數(shù)據(jù),后一個參數(shù)如果不填就是默認(rèn)取剩下的所有字符,無論是從前查還是從后查,執(zhí)行的字符串順序是不變的
示例:
--對abcde進(jìn)行截取,使用兩個參數(shù),從第一個字符開始截取字符串剩下所有的字符,即結(jié)果為abcde select substr('abcde',1) from dual ; --對abcde進(jìn)行截取,使用兩個參數(shù),從第三個字符開始截取字符串剩下所有的字符,即結(jié)果為cde select substr('abcde',3) from dual ; --對abcde進(jìn)行截取,使用兩個參數(shù),從倒數(shù)第二個字符開始從前往后截取字符串剩下所有的字符,即結(jié)果為de select substr('abcde',-2) from dual ; --對abcde進(jìn)行截取,使用三個參數(shù),從第一個字符開始截取字符串后三個字符,即結(jié)果為abc select substr('abcde',1,3) from dual ; --對abcde進(jìn)行截取,使用三個參數(shù),從第二個字符開始截取字符串后五個字符,但是因為不夠五個,所以就會輸出剩下所有的字符,即結(jié)果為bcde select substr('abcde',2,5) from dual ; --對abcde進(jìn)行截取,使用三個參數(shù),從倒數(shù)第四個字符開始從前往后截取字符串后三個字符,即結(jié)果為bcd select substr('abcde',-4,3) from dual
基本上用到了對字符串進(jìn)行截取的地方就會用到substr函數(shù),像上面說的對日期臟數(shù)據(jù)進(jìn)行處理就需要用到截取字符串,就用到了substr函數(shù)
可以通過instr函數(shù)和substr函數(shù)做一個分割函數(shù),將分割后的數(shù)據(jù)依次輸出,而不是這種通過輸出窗口才能看到的方法(見文末介紹oracle 在PL/SQL將字符串分割輸出)
下圖為分割函數(shù)主方法,用了insrt函數(shù),substr函數(shù)和Oracle的管道函數(shù),管道函數(shù)具體設(shè)置見這里
具體實現(xiàn)部分如下:
CREATE OR REPLACE FUNCTION test_Row_pipelined(p_insvar in varchar2, p_delimiter in varchar2) return test_Row_Type_TABLE pipelined as p_num integer := 1; p_num1 integer; p_length integer; p_start integer := 1; p_varchar varchar2(200); ret test_Row_Type; begin --如果不是以分隔符結(jié)尾的,就拼接上去 select case when (select count(1) from dual where p_insvar like '%' || p_delimiter) > 0 then p_insvar else p_insvar || p_delimiter end into p_varchar from dual; --整個字符串的長度 select length(p_varchar) into p_length from dual; --當(dāng)起始長度大于整體長度的時候 while (p_start <= p_length) loop --找到字符串與分隔符的分割后的下標(biāo) select instr(p_varchar, p_delimiter, p_start) into p_num1 from dual; --如果沒有,則返回全部字符串,說明到了結(jié)尾了 if p_num1 = 0 then ret := test_Row_Type(p_num, substr(p_varchar, p_start), p_num1, p_start, p_length, 0, 0); pipe row(ret); else --否則,截取字符傳中,從p_start開始找到下標(biāo)減去p_start的部分 ret := test_Row_Type(p_num, substr(p_varchar, p_start, p_num1 - p_start), p_num1, p_start, p_length, 0, 0); pipe row(ret); --新的起始點為下標(biāo)加1 p_start := p_num1 + 1; end if; --序號加1 p_num := p_num + 1; end loop; return; end;
結(jié)果如下:
oracle 在PL/SQL將字符串分割輸出
示例如下:
declare begin for maina in (select tt.line ll from (select regexp_substr('a,b,c,d', '[^,]+',1,level) line from dual bd connect by level <= regexp_count('a,b,c,d', ',') + 1) tt where rownum <= regexp_count('a,b,c,d', ',') + 1)loop dbms_output.put_line('分割后的字符:' || maina.ll); end loop; end;
結(jié)果:
到此這篇關(guān)于Oracle中instr函數(shù)與substr函數(shù)以及自制分割函數(shù)的文章就介紹到這了,更多相關(guān)oracle instr函數(shù)與substr函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Oracle在DML語句中使用returing?into子句
這篇文章介紹了Oracle在DML語句中使用returing?into子句的方法,文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-05-05oracle中行轉(zhuǎn)列LISTAGG()函數(shù)詳解及應(yīng)用實例
這篇文章主要給大家介紹了關(guān)于oracle中行轉(zhuǎn)列LISTAGG()函數(shù)詳解及應(yīng)用實例的相關(guān)資料,stagg是oracle11.2增加的特性,功能類似wmsys.wm_concat函數(shù),即將數(shù)據(jù)分組后,把指定列的數(shù)據(jù)通過指定符號合并,需要的朋友可以參考下2024-05-05DBA 在Linux下安裝Oracle Database11g數(shù)據(jù)庫圖文教程
正在學(xué)習(xí)Oracle DBA的知識,所以安裝oracle 11個的數(shù)據(jù)庫用以做測試,如Clone, RMAN, Stream等2014-08-08Oracle 11g自動診斷信息庫(Automatic Diagnostic Repository,ADR)概述
這篇文章主要介紹了Oracle11g的自動診斷信息庫,需要的朋友可以參考下2014-06-06Oracle?function函數(shù)返回結(jié)果集的3種方法
工作中常需要經(jīng)過一段復(fù)雜邏輯處理后,得出的一個結(jié)果集,所以這篇文章主要給大家介紹了關(guān)于Oracle?function函數(shù)返回結(jié)果集的3種方法,需要的朋友可以參考下2023-07-07Oracle11.2 命令行手工最簡創(chuàng)建數(shù)據(jù)庫的過程
Oracle 11.2命令行手工最簡創(chuàng)建數(shù)據(jù)庫的過程 命令行手工最簡創(chuàng)建數(shù)據(jù)庫的過程2009-09-09Oracle中的translate函數(shù)用法小結(jié)
translate提供了與replate函數(shù)相關(guān)的功能, replace讓你用一個字符串替換另一個字符串,以及刪除字符串,本文結(jié)合實例代碼給大家介紹的非常詳細(xì),感興趣的朋友一起看看吧2024-12-12