首页>资讯>正文
【Oracle】 instr函数与substr函数以及自制分割函数
2023-07-27 16:54:30    来源:博客园
Oracle instr函数与substr函数以及自制分割函数

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


【资料图】

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

instr函数

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

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

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

示例

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

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

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

示例:

--对abcde进行截取,使用两个参数,从第一个字符开始截取字符串剩下所有的字符,即结果为abcdeselect substr("abcde",1) from dual;--对abcde进行截取,使用两个参数,从第三个字符开始截取字符串剩下所有的字符,即结果为cdeselect substr("abcde",3) from dual;--对abcde进行截取,使用两个参数,从倒数第二个字符开始从前往后截取字符串剩下所有的字符,即结果为deselect substr("abcde",-2) from dual;--对abcde进行截取,使用三个参数,从第一个字符开始截取字符串后三个字符,即结果为abcselect substr("abcde",1,3) from dual;--对abcde进行截取,使用三个参数,从第二个字符开始截取字符串后五个字符,但是因为不够五个,所以就会输出剩下所有的字符,即结果为bcdeselect substr("abcde",2,5) from dual;--对abcde进行截取,使用三个参数,从倒数第四个字符开始从前往后截取字符串后三个字符,即结果为bcdselect 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;

结果如下:

关键词:

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

Oracleinstr函数与substr函数以及自制分割函数instr通常被用来作为判2023-07-27

全国校外教育培训监管与服务综合平台正式上线

记者7月24日从教育部获悉,全国校外教育培训监管与服务综合平台于当2023-07-27

创新高!山西省上半年非常规天然气总产量68.2亿立方米

据悉,今年以来山西省加快煤系非常规天然气增储上产,上半年非常规天然2023-07-27

脚步为啥有点匆忙 眼神还有点躲避

近日,深圳海关所属莲塘海关关员对一名进境旅客进行拦截检查,从其行李2023-07-12

PCB覆铜板又要涨价?!

根据财联社研究员发布消息,覆铜板提价通道或已开启,多家厂商已于近期2023-07-12

包头入选全国创新驱动示范市

近日,中国科协印发《关于支持2023—2025年度创新驱动示范市建设的通知2023-07-12

长安汽车申请深蓝GPT商标 长安汽车申请GPT相关商标

天眼查App显示,近日,长安汽车(000625)申请注册“SLGPT”“DEEPALGP2023-07-12

樱花园小学在徐汇排名_樱花园小学

1、70年前,徐汇区樱花园小学在乡民支持下落成,择名为“张氏小学”。22023-07-12

风吹稻浪谷飘香 各地紧抓农时奏响“丰收曲”

央视网消息:眼下,早稻大省江西的1800多万亩早稻全面开启收割。在赣州2023-07-12

男子为捡无人机被困山林 警民合力2小时成功营救

夏日炎炎,不少市民选择前往深山纳凉避暑,而近日,乐清一男子却因捡无2023-07-12

万达商管IPO,王健林的命运之战

在中国房地产领域,万达的王健林是一个独特的存在。当许家印、孙宏斌们2023-07-12

欧洲三大股指11日全线上涨 英国基准股指上涨0.12%

原标题:伦敦股市11日上涨  新华社伦敦7月11日电(记者杨海若)英国2023-07-12

实习计划范文 实习计划怎么写

1、为使本次教育实习顺利进行,圆满完成任务,故制定以下实习计划:一2023-07-12

殷海光文集(关于殷海光文集介绍)

1、《殷海光文集》是2009年湖北人民出版社出版的图书。2、作者是张斌峰2023-07-12

手势识别板块7月11日涨2.91%,睿能科技领涨,主力资金净流入2.65亿元

7月11日手势识别板块较上一交易日上涨2 91%,睿能科技领涨。当日上证指2023-07-12

曼联晒球员启程前往奥斯陆照片,将在热身赛中对阵利兹联

曼联官方更新社媒动态,晒出球员们启程前往奥斯陆的照片。北京时间明天2023-07-12

绿洲源拟设立全资子公司进一步整合优质资源

(原标题:绿洲源拟设立全资子公司进一步整合优质资源)07月11日,江西2023-07-11

消费日报|宝格丽官网将台湾列为国家、SASA美妆售假被罚没超90万

宝格丽官网将台湾列为国家近日,有网友爆料称,意大利高级珠宝品牌宝格2023-07-11

高甜!军营集体婚礼浪漫来袭

近日,武警北京总队执勤第十支队与中国融通旅发联合组织了一场军营集体2023-07-11

田野考古学_关于田野考古学介绍

田野考古学,关于田野考古学介绍这个很多人还不知道,我们一起来看看!12023-07-11

蚂蚁越过山丘

本文字数:2581|预计4分钟读完贴身肉搏,从未消停。来源丨虎嗅APP作者2023-07-11

德州市武城县:推进产业优化升级 促进工业高质量发展

中国山东网-感知山东7月11日讯(记者解义飞)7月11日,德州市人民政府2023-07-11

北京写字楼及零售市场均处缓慢恢复期,保险、证券及投资基金类企业表现活跃

北京写字楼及零售市场均处缓慢恢复期,保险、证券及投资基金类企业表现2023-07-11

戴德梁行:北京写字楼租赁市场处缓慢恢复期

证券时报记者张达戴德梁行今日发布的报告显示,二季度,北京写字楼市场2023-07-11

辽宁的医保基数2023年上涨多少?与养老金调整水平什么关系的?

辽宁的医保基数2023年上涨多少?与养老金调整水平什么关系的?随社保网2023-07-11