MySQL如何实现oracle函数INSTR的功能

下文我给大家简单讲讲关于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.00SQL> select instr('This is belong to you, but not to me.','to',1,2) as pos from dual;                 POS                              --------------------                                                32                              已用时间:  00: 00: 00.00SQL> select instr('This is belong to you, but not to me.','belong',-1,1) as pos from dual;                 POS                              --------------------                                                 9                              已用时间:  00: 00: 00.00SQL> 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) UNSIGNEDBEGIN      -- 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的功能这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。