【原创】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，如需转载请自行联系原作者