【原創】oracle函數INSTR的MySQL實現

標籤：oracle   mysql   instr   

一個遷移項目遇到的，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.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 ;

本文出自 “上帝，咱們不見不散！” 部落格，請務必保留此出處http://yueliangdao0608.blog.51cto.com/397025/1719867

【原創】oracle函數INSTR的MySQL實現