下文我给大家简单讲讲关于MySQL如何实现oracle函数INSTR的功能，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完MySQL如何实现oracle函数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.00MySQL里效果如下，
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的功能这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。
