当前位置:首页 > 开发 > 数据库 > 正文

oralce函数substr和mysql函数substring_index小记

发表于: 2014-01-23   作者:53873039oycg   来源:转载   浏览:
摘要:      最近偶尔在mysql数据库中看到了substring_index函数,先简单介绍下mysql的substring_index函数。    substring_index(str,delim,count) 返回字符串str中在第 count 个出现的分隔符 delim之前的子串,和java中的str.substring

     最近偶尔在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简单介绍,文章有点简单,但毕竟是原创,转载请注明出处。
      全文完,写的不好的地方请见谅,大神请绕过。

 

 

 

 

 

 

 

 

 

 

 

 

  

    

oralce函数substr和mysql函数substring_index小记

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
SUBSTR Syntax substr::= Description of the illustration substr.gif Purpose The SUBSTR functio
一、nvl()函数---处理空值 1、没用nvl函数处理前 select ename,comm as c from emp; 2、使用nvl函数
 Pivot   如您所知,关系表是表格化的,即,它们以列-值对的形式出现。假设一个表名为 CUSTOMERS
Oralce SQL语言常用函数 字符函数 这个函数一般接收字符作为参数,并且可以返回字符或数字 其中最常
1 function outer(){ 2 function inner(){} 3 return inner; 4 } 5 6 var a=outer(); 7 var b=outer
substr() 函数: 返回字符串的一部分。   语法:substr(string,start,length)   必需 - 规定在
转换函数 转换函数主要是用于操作多种数据类型,它的作用是把数据从一种数据类型转换 为另一种数据
转换函数 转换函数主要是用于操作多种数据类型,它的作用是把数据从一种数据类型转换 为另一种数据
日期函数 主要是操作DATE类型的数据,它的输入参数大部分是DATE类型, 并且输出的参数也是DATE类型
数字函数 数字函数是对数字数据进行一些算术运算的一些函数 函数的输入参数是数字类型,输出参数也
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号