Oracle中的instr和substr函数

Oracle中的instr和substr函数

Oracle PL/SQL中可以通过instr（获取特定字符串的索引）和substr（截取字符串）函数的组合使用进行字符串的截取实现类似于MySQL中substring_index函数或C#语言的Split函数。首先来认识下Oracle中的instr和substr函数。

1、instr函数

instr( string1,string2 [, start_position [, nth_appearance ] ] )

string1 源字符串

string2 目标字符串.

start_position 从string1 的哪个位置开始查找。默认为1.字符串索引从1开始。为正，从左到右开始检索，为负，从右到左检索。

nth_appearance 代表要查找第几次出现的string2.此参数可选，默认为 1.不能为负数

返回要查找的字符串string2在源字符串string1中的符合条件的开始索引

示例：

selectinstr('www.baidu.com', '.',-1,2) from dual

.在str中存在,-1说明检索是从最后一位开始,2说明出现2次,返回的结果是4

2、substr函数

substr(string,start_position,length)

string 源字符串,即被截取的字符串.

start_position 字符截取的开始位置.start_position大于0时,从左边算起,小于0时,从右边查起

length 截取字符的个数.默认截取到最后一位.

示例：

selectsubstr('www.baidu.com',1,4) from dual

执行结果都是'www.'

 

使用instr和substr函数实现MySQL中的substring_index函数

首先看第一个例子：

MySQL中：

select substring_index('www.baidu.com','.', 2);

Oracle中可以：

select substr('www.baidu.com', 1,instr('www.baidu.com', '.',1,2)-1) from dual

instr函数再-1是因为mysqlsubstring_index返回结果不包括.，上面返回的结果都是'www.baidu'。

看第二个例子：

MySQL中：

select substring_index('www.baidu.com','.', -2);

Oracle可以:

select substr('www.baidu.com',instr('www.baidu.com', '.',-1,2) + 1, length('www.baidu.com'))from dual

select substr('www.baidu.com',instr('www.baidu.com', '.',-1,2) + 1) from dual

 

使用instr和substr函数实现C#中的Split函数

         实现字符串按某字符截取的思路包括：instr获取该字符的索引；substr按索引截取字符串。获取字符串索引时要考虑字符串的头尾，并且要考虑字符串不存在分割字符的情况。实现的getstr函数如下，另外要考虑大字符串对象作为输入输出的情况，后面实现了getClobSt函数。

create or replace function getStr (

 inString IN varchar2,

 delimiter IN varchar2,

 substrNum IN INTEGER)

 return VARCHAR2

as

outStr varchar2(500);

begin

if instr(inString, delimiter,1,1) > 1then

 case

   when substrNum = 1

   then outStr := substr(inString, 1, instr(inString, delimiter,1,1)-1);

   when substrNum > 1 and instr(inString, delimiter,1,substrNum) > 0

   then outStr := substr(inString, instr(inString,delimiter,1,substrNum-1)+1, instr(inString, delimiter,1,substrNum)-instr(inString,delimiter,1,substrNum-1)-1);

   when substrNum > 1 and instr(inString, delimiter,1,substrNum) = 0 andinstr(inString, delimiter,1,substrNum-1) > 0

   then outStr := substr(inString, instr(inString,delimiter,1,substrNum-1)+1, length(inString)-instr(inString,delimiter,1,substrNum-1));

   when substrNum > 1 and instr(inString, delimiter,1,substrNum) = 0 andinstr(inString, delimiter,1,substrNum-1) = 0

   then outStr := '';

  endcase;

else

 outStr := inString;

end if;

return outStr;

end;

/

 

create or replace function getClobStr (

 inString IN CLOB,

 delimiter IN varchar2,

 substrNum IN INTEGER)

 return CLOB

as

outStr CLOB;

begin

if instr(inString, delimiter,1,1) > 1then

 case

   when substrNum = 1

   then outStr := substr(inString, 1, instr(inString, delimiter,1,1)-1);

   when substrNum > 1 and instr(inString, delimiter,1,substrNum) > 0

   then outStr := substr(inString, instr(inString,delimiter,1,substrNum-1)+1, instr(inString, delimiter,1,substrNum)-instr(inString,delimiter,1,substrNum-1)-1);

   when substrNum > 1 and instr(inString, delimiter,1,substrNum) = 0 andinstr(inString, delimiter,1,substrNum-1) > 0

   then outStr := substr(inString, instr(inString,delimiter,1,substrNum-1)+1, length(inString)-instr(inString,delimiter,1,substrNum-1));

   when substrNum > 1 and instr(inString, delimiter,1,substrNum) = 0 andinstr(inString, delimiter,1,substrNum-1) = 0

   then outStr := '';

  endcase;

else

 outStr := inString;

end if;

return outStr;

end;

/