Example of how Mysql implements the INSTR function of Oracle

If our project is to be migrated from Oracle to Mysql, but the instr function of MySQL can only find whether the child string is in the parent string, it cannot be searched according to the number of occurrences.

Here I wrote one for migration. Of course, here I only focus on migration, and may not fully implement the details of the original function.


Oracle uses the following calls several times,


SQL> select instr ('this is belong to you, but not to me. ', 'to',) as pos from dual;
 
 
POS
--------------------
16
 
 
Used Time: 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
 
 
Used Time: 00: 00: 00.00
SQL> select instr ('this is belong to you, but not to me. ', 'belong',-) as pos from dual;
 
 
POS
--------------------
                   9                                
 
 
Used Time: 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                                
 
 
Used Time: 00: 00: 00.00



The results in MySQL are as follows,


Mysql> select func_1__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_1__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_1__oracle ('this is belong to you, but not to me. ', 'belong',-) as pos;
+ ------ +
| Pos |
+ ------ +
| 9 |
+ ------ +
1 row in set (0.00 sec)
 
 
Mysql> select func_1__oracle ('this is belong to you, but not to me. ', 'belong',-) as pos;
+ ------ +
| Pos |
+ ------ +
| 0 |
+ ------ +
1 row in set (0.00 sec)



The code for func_instr_oracle function is attached:

DELIMITER $ USE 'oracle12c' $ drop function if exists 'func _ cmd_oracle '$ create definer = 'root' @ 'localhost' FUNCTION 'func _ cmd_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; 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; SET I = I-1; END WHILE; -- Equal to 0. else return 0; end if; RETURN 0; END $ DELIMITER;

Instr method for replacing Oracle in MySql

-- Function "Release 2" ddl create function 'release 2' (v_string varchar (5000), v_delimiter varchar (20), pos int, nth int) RETURNS varchar (5000) begin declare icount int default 0; declare len int default 0; declare len1 int default 0; declare lth int default 0; declare lth1 int default 0; declare str1 varchar (5000) default ''; set len = length (v_string); set len1 = length (v_delimiter); set lth = instr (v_string, v_delimiter); if lth = 0 then set icount = lth; else if pos is null then set icount = lth; elseif pos <0 then set icount = locate (v_delimiter, v_string, len + pos-1); elseif pos = 0 then set icount = 0; elseif pos = 1 then if nth is null then set icount = lth; elseif nth> = 1 then set icount = if (length (substring_index (v_string, v_delimiter, nth )) = 0 or length (substring_index (v_string, v_delimiter, nth) = length (v_string), 0, length (substring_index (v_string, v_delimiter, nth) + 1 ); else set icount = 0; end if; else if (nth is null) or (nth = 1) then set icount = locate (v_delimiter, v_string, pos ); elseif nth> 1 then set str1 = substring (v_string, pos); set icount = if (length (substring_index (str1, v_delimiter, nth) = 0 or length (substring_index (str1, v_delimiter, nth) = length (str1), 0, length (substring_index (str1, v_delimiter, nth); if icount <> 0 then set icount = icount + pos; end if; return icount; end;