最近偶尔在mysql数据库中看到了substring_index函数,先简单介绍下mysql的substring_index函数。
substring_index(str,delim,count) 返回字符串str中在第 count 个出现的分隔符 delim之前的子串,和java中的str.substring函数类似。 如果count是正数时候,substring_index是从左到右的顺序检查分隔符delim所在的位置。 如果count是负数时候,substring_index是从右到左的顺序检查分隔符delim所在的位置,返回的是从字符串str左边下标1开始的下标位置。 特殊点如果count等于0,那么,不管delim是否在str中存在,你得到的将是一个空白字符串。如下所示
select substring_index('www.baidu.com', '.', 0); select substring_index('www.baidu.com', '-', 0);
上面说的很抽象,来个简单的例子。
select substring_index('www.baidu.com', '.', 2);
2为正数,.在str中存在,返回的结果是从下标1到第2次.出现的位置之间substr(1,10-1)的子串。所以结果是'www.baidu'。
select substring_index('www.baidu.com', '.', -2);
-2为负数,.在str中存在,.在str中搜索顺序是从右到左第2次出现的位置是4,用于是负数返回的是从最后到4+1的子串,也就是substr(str,4+1),所以结果是baidu.com。
可以看到substring_index返回的子串是不包含分隔符 delim在str中第count次匹配的位置的,如果分隔符delim在str中出现的位置小于count次会发生什么呢?来测试下:
select substring_index('www.baidu.com', '.', 3); select substring_index('www.baidu.com', '.', -3);
运行之后可以看到返回的结果都是'www.baidu.com'。再来测试delim在str中不存在的情况:
select substring_index('www.baidu.com', '-', -1); select substring_index('www.baidu.com', '-',1);
运行之后可以看到返回的结果都是'www.baidu.com',从上面的测试可以知道分隔符delim在str中不存在或者出现次数小于指定的count时将返回str本身。上面只是测试了英文数据,中文数据测试也是一样的,测试如下:
select substring_index('我是中文_测试_数据','_',2) from dual;--我是中文_测试 select substring_index('我是中文_测试_数据','_',-2) from dual;--测试_数据 select substring_index('我是中文_测试_数据','_',0) from dual;--空白字符串 select substring_index('我是中文_测试_数据','_',3) from dual;--我是中文_测试_数据
中英文混合的情况我就不测试了。
mysql有substring_index函数,oracle是否也要类似的函数呢?
我所知道的oracle中常见的只有substr和mysql的substring_index函数类似,有网友知道oralce其他函数的,麻烦留言告知。mysql中也有substr,关于两者的区别,后面我会简述。
先简单的介绍下oracle的substr函数和instr函数,后面我会用这2个函数模拟mysql的substring_index函数
instr( string1, string2 [, start_position [, nth_appearance ] ] ) string1 源字符串 string2 目标字符串. start_position 从string1 的哪个位置开始查找。默认为1. 字符串索引从1开始。为正,从左到右开始检索,为负,从右到左检索。 nth_appearance 代表要查找第几次出现的string2. 此参数可选,默认为 1.不能为负数 返回要查找的字符串string2在源字符串string1中的符合条件的开始索引
substr(string,start_position,length) string 源字符串,即被截取的字符串. start_position 字符截取的开始位置.start_position大于0时,从左边算起,小于0时,从右边查起 length 截取字符的个数.默认截取到最后一位.
先来个测试:
select instr('www.baidu.com', '.',-1,2) from dual
.在str中存在,-1说明检索是从最后一位开始,2说明出现2次,返回的结果是4
select instr('www.baidu.com', '.',1,2) from dual
.在str中存在,1说明按从左到右的顺序,2说明出现2次,返回的结果是10
下面来点特殊的测试,先测试nth_appearance,不是说不能为负数吗,我测试nth_appearance=0看下
select instr('www.baidu.com', '.',1,0) from dual select instr('www.baidu.com', '.',-1,0) from dual
你们猜结果是什么?结果是ORA-01428:参数'0'超出范围,说明nth_appearance不能为0,只能大于0。
再测试start_position,令start_position=0看下
select instr('www.baidu.com', '.',0,1) from dual select instr('www.baidu.com', '-',0,1) from dual select instr('www.baidu.com', '.',0,2) from dual select instr('www.baidu.com', '-',0,2) from dual
测试结果都是0;如果把start_position改为>0,结果如下
select instr('www.baidu.com', '.',1,1) from dual--4 select instr('www.baidu.com', '-',1,1) from dual--0 select instr('www.baidu.com', '.',1,2) from dual--10 select instr('www.baidu.com', '-',1,2) from dual--0
如果把start_position改为<0,结果如下:
select instr('www.baidu.com', '.',-1,1) from dual--10 select instr('www.baidu.com', '-',-1,1) from dual--0 select instr('www.baidu.com', '.',-1,2) from dual--4 select instr('www.baidu.com', '-',-1,2) from dual--0 select instr('www.baidu.com', '.',-1,3) from dual--0 select instr('www.baidu.com', '-',-1,3) from dual--0 select instr('www.baidu.com', '.',-1,3) from dual--0 select instr('www.baidu.com', '-',-1,3) from dual--0
可以看出instr函数目标字符串在str中不存在时候或者出现次数小于给定的次数时,返回的都是0;
使用中文测试也是一样:
select instr('我是中文_测试_数据','_',1,0) from dual;--ORA-01428:参数'0'超出范围 select instr('我是中文_测试_数据','_',1,2) from dual;--8 select instr('我是中文_测试_数据','_',1,3) from dual;--0 select instr('我是中文_测试_数据','_',-1,2) from dual;--5 select instr('我是中文_测试_数据','_',-1,3) from dual;--0
下面测试下substr。
select substr('www.baidu.com',0,4) from dual select substr('www.baidu.com',1,4) from dual
上面2个执行结果都是'www.'。如果上面是在mysql下测试,结果如下:
select substr('www.baidu.com',0,4) from dual--空白字符串 select substr('www.baidu.com',1,4) from dual--www.
从这里可以看出mysql中substr开始位置不能为0,而oracle下从0开始或者从1开始结果是一样的,下面继续oracle测试。
select substr('www.baidu.com',1) from dual select substr('www.baidu.com',1,22) from dual
返回结果是'www.baidu.com'。
select substr('www.baidu.com',-1) from dual select substr('www.baidu.com',-1,4) from dual select substr('www.baidu.com',-1,22) from dual
返回的结果是'm',说明substr返回的是从开始位置到str最后一位。
使用中文测试如下:
select substr('我是中文_测试_数据',1) from dual--我是中文_测试_数据 select substr('我是中文_测试_数据',1,22) from dual--我是中文_测试_数据 select substr('我是中文_测试_数据',-1) from dual--据 select substr('我是中文_测试_数据',-1,4) from dual--据 select substr('我是中文_测试_数据',-1,22) from dual--据
从上面的测试可以看出oracle下instr函数和mysql的substring_index函数很像,instr函数已经可以得到下标了,结合substr可以截取子串返回。
Mysql的
select substring_index('www.baidu.com', '.', 2);
Oralce可以这样做:
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
上面可以不使用length函数,因为oracle的substr默认截取到str最后一位。
中文测试如下:
Mysql下面:
select substring_index('我是中文_测试_数据','_',1) from dual;--我是中文 select substring_index('我是中文_测试_数据','_',2) from dual;--我是中文_测试 select substring_index('我是中文_测试_数据','_',-2) from dual;--测试_数据 select substring_index('我是中文_测试_数据','_',0) from dual;--空白字符串 select substring_index('我是中文_测试_数据','_',3) from dual;--我是中文_测试_数据 select substring_index('我是中文_测试_数据','.',3) from dual;--我是中文_测试_数据
Oracle下面:
select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,1)-1) from dual--我是中文 select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,2)-1) from dual--我是中文_测试 select substr('我是中文_测试_数据', instr('我是中文_测试_数据', '_',-1,2) + 1) from dual--测试_数据 select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,0)-1) from dual--ORA-01428:参数'0'超出范围 select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,3)-1) from dual--空白 select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '.',1,3)-1) from dual--空白
可以看到我写的这个有点问题,如果想在nth_appearance(>0)大于目标串在str中出现的次数时和mysql一样返回整个字符串,可以这样写:
select substr('我是中文_测试_数据', 1, (select (case when instr('我是中文_测试_数据', '_', 1, 3) <= 1 then (select length('我是中文_测试_数据') from dual) else (instr('我是中文_测试_数据', '_', 1, 3) - 1) end) from dual)) from dual
这个在nth_appearance=0时用不了,进一步,考虑到nth_appearance=0时候返回NULL可以这样做,返回''这样的字符串使用substr做不了。
select substr('我是中文_测试_数据', 1, (select (case when 0 = 0 then -1 else case when instr('我是中文_测试_数据', '_', 1, 3) <= 1 then (select length('我是中文_测试_数据') from dual) else (instr('我是中文_测试_数据', '_', 1, 3) - 1) end end) from dual)) from dual
上面的sql是不是很丑陋,,我们可以定义一个存储过程封装一下。
create or replace procedure my_substring_index(str in varchar2, delim in varchar2, in_count in int, out_str out varchar2) as v_time int; v_delim varchar2(20); begin if str is null then out_str := '空白字符串'; elsif length(str) = 0 then out_str := '空白字符串'; end if; if delim is null then v_delim := ' '; elsif length(delim) = 0 then v_delim := ' '; else v_delim := delim; end if; if in_count = 0 then out_str := '空白字符串'; elsif in_count>0 then v_time := instr(str, delim, 1, in_count); else v_time := instr(str, delim, -1, (-1)*in_count); end if; if v_time = 0 then out_str := str; end if; if in_count>0 then if v_time = 1 then out_str := '空白字符串'; elsif v_time > 1 then select substr(str, 1, v_time - 1) into out_str from dual; end if; elsif in_count<0 then if v_time >= 1 then select substr(str, v_time + 1) into out_str from dual; end if; end if; end my_substring_index;
Oracle上面测试过程如下:
在My Object下面的Procedures下找到存储过程my_substring_index,点击右键选择Test.出来Oracle的测试脚本:
begin -- Call the procedure my_substring_index(str => :str, delim => :delim, in_count => :in_count, out_str => :out_str); end;
在测试脚本页面下面填上具体值,按F8,不出意外的化可以看到返回值。
如果想自己写脚本可以这样,新建一个sql文件,内容如下:
declare v_str varchar2(50); v_delim varchar2(20); v_in_count number; v_out_str varchar2(20); begin v_str:='&str'; v_delim:='&delim'; v_in_count:=&in_count; my_substring_index(str => v_str, delim => v_delim, in_count => v_in_count, out_str => v_out_str); dbms_output.put_line('result='||v_out_str); end;
在命令行下运行:
SQL> set serveroutput on; SQL> start f:/saveFile/test.sql 16 / result=www.baidu PL/SQL procedure successfully completed
如果想直接在cmd window下测试,可以这样:
SQL> set serveroutput on; SQL> var v_str varchar2(50); SQL> var v_delim varchar2(20); SQL> var v_in_count number; SQL> var v_out_str varchar2(50); SQL> exec :v_str:='我是中文_测试_数据'; SQL> exec :v_delim:='_'; SQL> exec :v_in_count:=2; SQL> exec my_substring_index(:v_str,:v_delim,:v_in_count,:v_out_str); SQL> print v_out_str;
以上是我写的oralce函数substr和mysql函数substring_index简单介绍,文章有点简单,但毕竟是原创,转载请注明出处。
全文完,写的不好的地方请见谅,大神请绕过。
相关推荐
### Oracle中的SUBSTR函数详解 在Oracle数据库中,`SUBSTR`函数是一个非常重要的字符串处理函数,用于从指定的字符串中提取子串。该函数在实际应用中极为广泛,能够帮助用户灵活地处理数据,满足各种业务需求。下面...
在Oracle数据库中,`SUBSTR`和`INSTR`函数是非常重要的字符串处理函数,它们在数据操作和查询中发挥着关键作用。本文将详细介绍这两个函数的用法,并结合`CASE WHEN`语句来解决特定的问题。 `SUBSTR`函数用于从一个...
Oracle数据库是世界上最流行的数据库管理系统之一,它提供了丰富的内置函数,以支持复杂的查询和数据操作。这份"oracle函数大全(分类显示).zip"压缩包显然是一份详细的Oracle函数参考手册,对于初学者而言,是一个...
截取的字符串为15,151,152,16’,可以看作是ip吧(虽然这里指的不是iP), 然后要截取每个逗号之前那部分 以下的意思是:在字符串中以逗号为索引,获取...SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16',',',
"Oracle函数大全(CHM格式).rar"这个压缩包显然包含了关于Oracle数据库中各种函数的详细信息,对于学习和查询Oracle函数是非常宝贵的资源。 CHM(Compiled Help Manual)格式是一种由Microsoft开发的帮助文件格式...
REGEXP_SUBSTR函数格式如下: function REGEXP_SUBSTR(string, pattern, position, occurrence, modifier) string :需要进行正则处理的字符串 pattern :进行匹配的正则表达式 position :起始位置,从第几个...
本资源是一份Oracle函数大全的手册,包含丰富的参考函数和速查信息,以CHM(Microsoft Compiled HTML Help)格式提供,便于用户离线查阅。 Oracle函数是数据库操作中的核心组成部分,它们允许开发人员和DBA执行各种...
截取字符串方面,Oracle 使用 SUBSTR 函数,而 MySQL 使用 SUBSTRING 函数。在 MySQL 中,从子句后的表如果是子查询形式,那么后面必须有别名。 事务 Oracle 很早就完全支持事务。MySQL 在 InnoDB 存储引擎的行级...
"orafce-3.1.2"是这个项目的某个版本,它包含了多个与Oracle相似的函数和过程。例如: 1. **日期和时间函数**:Oracle中的ADD_MONTHS、LAST_DAY、NEXT_DAY等在orafce中都有对应的实现,帮助用户处理日期和时间数据...
Oracle数据库系统提供了丰富的内置函数,这些函数用于处理各种数据类型,包括字符串、数值以及日期等。以下是关于Oracle函数的一些详细说明: 1. ASCII函数:ASCII函数返回与输入字符相对应的ASCII码,即十进制数字...
Oracle数据库系统是世界上最广泛使用的数据库管理系统之一,其强大的功能之一在于丰富的内置函数。...查阅"oracle函数大全.chm"手册,将提供更详细的信息和实例,助你在实践中更好地应用这些函数。
本文介绍的`fnc_table_to_mysql`函数能够有效地帮助用户从Oracle迁移到MySQL时,快速地生成对应的表结构。尽管存在一定的局限性,但通过调整参数和手动检查生成的结果,可以有效地满足大多数迁移场景的需求。
除了`substring`和`patindex`,SQL还提供了其他字符串处理函数,如MySQL中的`SUBSTRING`和`SUBSTR`,以及`substring_index`,它们有着类似的用途,但语法和使用方式可能有所不同。例如,在MySQL中,`SUBSTRING`函数...
"Oracle函数大全中文API文档"是一个非常实用的资源,它提供了对Oracle数据库所有内置函数的详尽解释,帮助开发者和DBA快速理解和使用这些函数。 1. **日期与时间函数**: Oracle提供了处理日期和时间的大量函数,...
Oracle函数是实现数据处理和查询的重要工具,对于任何希望深入理解和掌握Oracle数据库编程的人来说,理解并熟练运用这些函数是必不可少的。 在Oracle数据库中,函数可以分为几大类,包括内置函数、数学函数、字符串...
这个包包含了各种过程和函数,用于创建、读取、写入、修改和删除LOB数据。 `DBMS_LOB`包中的主要功能包括: 1. **创建LOB**: - `CREATE_BFILE`:在Oracle外部文件系统中创建一个BFILE(Binary File)对象,它...
在Oracle数据库中,函数可以分为多种类型,包括标量函数、聚合函数、内置数学函数、字符串函数、日期和时间函数、转换函数、系统信息函数等。下面将对这些函数类别进行详细介绍: 1. **标量函数**:这些函数返回...
首先,"ORACLE函数大全.doc"很可能是整个文档集的总览,包含Oracle数据库中的各类函数的概览和分类,可能包括数学函数、字符串函数、日期时间函数、转换函数、条件函数、系统信息函数等。这些函数在SQL查询和PL/SQL...
同时,这个函数也可以与其他 Oracle 函数和操作符结合使用,以实现更加复杂的数据处理任务。 在实际应用中,Oracle 中实现 Split 函数功能可以用于各种数据处理场景,例如数据清洁、数据转换和数据分析等。例如,在...