【原创】oracle函数INSTR的MySQL实现

一个迁移项目遇到的，MySQL的instr函数只能查找子串是否在父串中，没法按照出现的次数进行查找。 

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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里效果如下，

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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的代码：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

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 ;

本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/1719867 ，如需转载请自行联系原作者