Mysql实现Oracle的INSTR函数的方法实例

作者:袖梨 2022-11-14

如果我们的项目要从Oracle迁移到Mysql,但MySQL的instr函数只能查找子串是否在父串中,没法按照出现的次数进行查找。

这里我自己写了一个,以便迁移。当然我这里仅仅针对的是迁移,可能没有完全实现原有函数的细节。


Oracle 里用了几次如下的调用,


SQL> select instr('This is belong to you, but not to me.','to',1,1) as pos from dual;


POS
--------------------
16


已用时间: 00: 00: 00.00
SQL> select instr('This is belong to you, but not to me.','to',1,2) as pos from dual;


POS
--------------------
32


已用时间: 00: 00: 00.00
SQL> select instr('This is belong to you, but not to me.','belong',-1,1) as pos from dual;


POS
--------------------
9


已用时间: 00: 00: 00.00
SQL> select instr('This is belong to you, but not to me.','belong',-1,2) as pos from dual;


POS
--------------------
0


已用时间: 00: 00: 00.00



MySQL里效果如下,


mysql> select func_instr_oracle('This is belong to you, but not to me.','to',1,1) as pos;
+------+
| pos |
+------+
| 16 |
+------+
1 row in set (0.00 sec)


mysql> select func_instr_oracle('This is belong to you, but not to me.','to',1,2) as pos;
+------+
| pos |
+------+
| 32 |
+------+
1 row in set (0.00 sec)


mysql> select func_instr_oracle('This is belong to you, but not to me.','belong',-1,1) as pos;
+------+
| pos |
+------+
| 9 |
+------+
1 row in set (0.00 sec)


mysql> select func_instr_oracle('This is belong to you, but not to me.','belong',-1,2) as pos;
+------+
| pos |
+------+
| 0 |
+------+
1 row in set (0.00 sec)



附上函数func_instr_oracle的代码:


DELIMITER $$  
  
  
USE `oracle12c`$$  
  
  
DROP FUNCTION IF EXISTS `func_instr_oracle`$$  
  
  
CREATE DEFINER=`root`@`localhost` FUNCTION `func_instr_oracle`(  
    f_str VARCHAR(1000), -- Parameter 1  
    f_substr VARCHAR(100),  -- Parameter 2  
    f_str_pos INT, -- Postion  
    f_count INT UNSIGNED -- Times  
    ) RETURNS INT(10) UNSIGNED  
BEGIN  
      -- Created by ytt. Simulating Oracle instr function.  
      -- Date 2015/12/5.  
      DECLARE i INT DEFAULT 0; -- Postion iterator  
      DECLARE j INT DEFAULT 0; -- Times compare.  
      DECLARE v_substr_len INT UNSIGNED DEFAULT 0; -- Length for Parameter 1.  
      DECLARE v_str_len INT UNSIGNED DEFAULT 0;  -- Length for Parameter 2.  
      SET v_str_len = LENGTH(f_str);   
      SET v_substr_len = LENGTH(f_substr);  
      -- Unsigned.  
      IF f_str_pos > 0 THEN  
        SET i = f_str_pos;  
        SET j = 0;  
        WHILE i <= v_str_len  
        DO  
          IF INSTR(LEFT(SUBSTR(f_str,i),v_substr_len),f_substr) > 0 THEN  
            SET j = j + 1;  
            IF j = f_count THEN  
              RETURN i;  
            END IF;  
          END IF;  
          SET i = i + 1;  
        END WHILE;  
      -- Signed.  
      ELSEIF f_str_pos <0 THEN  
        SET i = v_str_len + f_str_pos+1;  
        SET j = 0;  
        WHILE i <= v_str_len AND i > 0   
        DO  
          IF INSTR(RIGHT(SUBSTR(f_str,1,i),v_substr_len),f_substr) > 0 THEN  
            SET j = j + 1;  
            IF j = f_count THEN  
              RETURN i - v_substr_len + 1;  
            END IF;  
          END IF;  
          SET i = i - 1;  
        END WHILE;  
      -- Equal to 0.  
      ELSE  
        RETURN 0;  
      END IF;  
      RETURN 0;  
    END$$  
  
  
DELIMITER ;


MySql中代替Oracle的instr方法

-- Function "INSTR2" DDL
  CREATE FUNCTION `INSTR2`(v_string varchar(5000), v_delimiter varchar(20), pos int,nth int) RETURNS varchar(5000)
  begin
  declare icount int default 0;
  declare len int default 0;
  declare len1 int default 0;
  declare lth int default 0;
  declare lth1 int default 0;
  declare str1 varchar(5000) default '';
  set len = length(v_string);
  set len1 = length(v_delimiter);
  set lth = instr(v_string ,v_delimiter);
  if lth=0 then
  set icount = lth;
  else
  if pos is null then
  set icount = lth;
  elseif pos < 0 then
  set icount = locate(v_delimiter,v_string,len+pos-1);
  elseif pos = 0 then
  set icount = 0;
  elseif pos = 1 then
  if nth is null then
  set icount = lth;
  elseif nth >=1 then
  set icount = if (length(substring_index(v_string ,v_delimiter,nth))=0 or length(substring_index(v_string,v_delimiter ,nth))=length(v_string ),0,length(substring_index(v_string ,v_delimiter,nth))+1);
  else
  set icount = 0;
  end if;
  else
  if (nth is null) or (nth=1) then
  set icount = locate(v_delimiter,v_string,pos);
  elseif nth > 1 then
  set str1 = substring(v_string,pos) ;
  set icount = if (length(substring_index(str1 ,v_delimiter,nth))=0 or length(substring_index(str1,v_delimiter ,nth))=length(str1),0,length(substring_index(str1 ,v_delimiter,nth)));
  if icount<>0 then
  set icount = icount+pos;
  end if;
  end if;
  end if;
  end if;
  return icount;
  end;


相关文章

精彩推荐