oracle instr和mysql_Oracle 的INSTR函数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的代码：mysql

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 ;