Mysql實現Oracle的INSTR函數的方法實例

instr函數在Oracle/PLSQL中是返回要截取的字符串在源字符串中的位置。instr是一個非常好用的字符串處理函數，幾乎所有的字符串分隔都用到此函數。

如果我們的項目要從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;