MySQL implementation of "original" Oracle function instr

When a migration project is encountered, the MySQL InStr function can only find if the substring is in the parent string and cannot be found as many times as it appears.

Here I wrote one myself in order to migrate. Of course, I'm only here for migration, and may not fully implement the details of the original function.

Oracle uses several of the following calls,

Sql> select instr (' This is belong to you, but not to me. ') , ' to ',  as pos from dual;                  POS                                --------------------                                                  16                                 used time:   00: 00: 00.00sql > select inSTR (' This is belong to you, but not to me ', ' to ',  as pos)  from dual;                  POS                               -------------- ------                                                  32                                 used Time:  00: 00: 00.00sql> select  InStr (' This is beloNg to you, but not to me. ', ' belong ', -1,1)  as pos from dual;                  pos                                --------------------                                                    9                                 Time:   00: 00: 00.00sql> select instr (' This is  belong to yOu, but not to me. ', ' belong ', -1,2)  as pos from dual;                  POS                                --------------------                                                   0                                Time used:   00: 00: 00.00

The results in MySQL are as follows,

Mysql> select func_instr_oracle (' This is belong to you, but not  to me ', ' to ',  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 ',  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 ',-  as pos;+------+| pos  |+------+|    0 |+------+1 row in set  (0.00  SEC)

Enclose the code for the function func_instr_oracle:

delimiter $ $USE   ' oracle12c ' $ $DROP  FUNCTION IF EXISTS  ' func_instr_oracle ' $ $CREATE  definer= ' root ' @ ' localhost '  FUNCTION  ' func_instr_oracle ' (    f_str  VARCHAR (+),  -- parameter 1    f_substr varchar (+),   --  Parameter 2    f_str_pos INT, -- Postion     f_count int unsigned -- times    )  returns int (Ten)   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  ;

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1719867

MySQL implementation of "original" Oracle function instr