【Oracle】 instr函数与substr函数以及自制分割函数

Oracle instr函数与substr函数以及自制分割函数

instr通常被用来作为判断某个字符串中是否含有执行字符串和将返回结果作为一些数据分割的数据，即有模糊查询like的作用，当返回的查询结果的序号为0的时候就是没有匹配上

substr函数就是很简单明了，就是个截取字符函数

instr函数

通常使用instr函数有三种格式

1. instr(字符串,要匹配的字符串)
2. instr(字符串,要匹配的字符串,起始位置)
3. instr(字符串,要匹配的字符串,起始位置,匹配次序)

实质上都是instr(字符串,要匹配的字符串,起始位置,匹配次序)，只不过前两个参数一定要有数据，后两个参数如果不填就默认为1，无论是从前查还是从后查，执行的字符串顺序是不变的

示例

--在ababc中查找a，只有两个参数的时候，起始位置和匹配次序默认为1，即从位置1开始，查询第一次匹配的位置，即结果为1
select instr('ababc','a') from dual
;
--在ababc中查找a，只有三个参数的时候，匹配次序默认为1，起始位置填写2的话，即从位置2开始，查询第一次匹配的位置，即结果为3
select instr('ababc','a',2) from dual
;
--在ababc中查找a，有四个参数的时候，起始位置填写1，匹配次序填写2，即从位置1开始，查询第二次匹配的位置，即结果为3
select instr('ababc','a',1,2) from dual
;
--在ababc中查找a，有四个参数的时候，起始位置填写2，匹配次序填写2，即从位置2开始，查询第二次匹配的位置，查询不到结果，即结果为0
select instr('ababc','a',2,2) from dual
;
--在ababc中查找a，有四个参数的时候，起始位置填写-1，匹配次序填写1，即从位置5(-1就是从后往前查询)开始，查询第一次匹配的位置，即结果为3
select instr('ababc','a',-1,1) from dual
;
--在ababc中查找ab，只有两个参数的时候，起始位置和匹配次序默认为1，即从位置1开始，查询第一次匹配的a(ab的开头是a)位置，即结果为1
select instr('ababc','ab') from dual
;
--在ababc中查找ab，只有三个参数的时候，匹配次序默认为1，起始位置填写2的话，即从位置2开始，查询第一次匹配的a(ab的开头是a)位置，即结果为3
select instr('ababc','ab',2) from dual
;
--在ababc中查找ab，有四个参数的时候，起始位置填写1，匹配次序填写2，即从位置1开始，查询第二次匹配的a(ab的开头是a)位置，即结果为3
select instr('ababc','ab',1,2) from dual
;
--在ababc中查找ab，有四个参数的时候，起始位置填写2，匹配次序填写2，即从位置2开始，查询第二次匹配的a(ab的开头是a)位置，查询不到结果，即结果为0
select instr('ababc','ab',2,2) from dual
;
--在ababc中查找ab，有四个参数的时候，起始位置填写-1，匹配次序填写1，即从位置5(-1就是从后往前查询)开始，查询第一次匹配的a(ab的开头是a)位置，即结果为3
select instr('ababc','ab',-1,1) from dual
;
--在ababc中查找ba，有四个参数的时候，起始位置填写-1，匹配次序填写1，即从位置5(-1就是从后往前查询)开始，查询第一次匹配的b(ba的开头是b)位置，即结果为1
select instr('ababc','ba',-1,1) from dual

可以根据instr函数做很多事情

比如有时候会有一些日期脏数据，格式很混乱，则就可以直接使用instr去匹配进行处理

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

还有like的作用，下面两个语句结果是相同的

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函数

通常使用substr函数有三种格式

1. substr(字符串,起始位置)
2. substr(字符串,起始位置,截取字符数)

实质上都是substr字符串,起始位置,截取字符数)，只不过前两个参数一定要有数据，后一个参数如果不填就是默认取剩下的所有字符，无论是从前查还是从后查，执行的字符串顺序是不变的

示例：

--对abcde进行截取，使用两个参数，从第一个字符开始截取字符串剩下所有的字符，即结果为abcde
select substr('abcde',1) from dual
;
--对abcde进行截取，使用两个参数，从第三个字符开始截取字符串剩下所有的字符，即结果为cde
select substr('abcde',3) from dual
;
--对abcde进行截取，使用两个参数，从倒数第二个字符开始从前往后截取字符串剩下所有的字符，即结果为de
select substr('abcde',-2) from dual
;
--对abcde进行截取，使用三个参数，从第一个字符开始截取字符串后三个字符，即结果为abc
select substr('abcde',1,3) from dual
;
--对abcde进行截取，使用三个参数，从第二个字符开始截取字符串后五个字符，但是因为不够五个，所以就会输出剩下所有的字符，即结果为bcde
select substr('abcde',2,5) from dual
;
--对abcde进行截取，使用三个参数，从倒数第四个字符开始从前往后截取字符串后三个字符，即结果为bcd
select substr('abcde',-4,3) from dual

基本上用到了对字符串进行截取的地方就会用到substr函数，像上面说的对日期脏数据进行处理就需要用到截取字符串，就用到了substr函数

可以通过instr函数和substr函数做一个分割函数，将分割后的数据依次输出，而不是这种通过输出窗口才能看到的方法

下图为分割函数主方法，用了insrt函数，substr函数和Oracle的管道函数，管道函数具体设置见这里

具体实现部分如下：

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
  --如果不是以分隔符结尾的，就拼接上去
  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;
  --当起始长度大于整体长度的时候
  while (p_start <= p_length) loop
    --找到字符串与分隔符的分割后的下标
    select instr(p_varchar, p_delimiter, p_start) into p_num1 from dual;
    --如果没有，则返回全部字符串，说明到了结尾了
    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开始找到下标减去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);
     --新的起始点为下标加1
      p_start := p_num1 + 1;
    
    end if;
    --序号加1
    p_num := p_num + 1;
  end loop;
  return;
end;

结果如下：

20240307更新

对于要将这个分割管道函数的数据一并写入语句计算的，可以直接将其作为表，并到语句中，同时关联字段作为参数

ttt20240307表数据：

select d.docname,t.test2 from ttt20240307 d,table(test_Row_pipelined(d.docname,'_')) t

结果：