如果我们的项目要从Oracle迁移到Mysql,但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.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.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) 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方法
-- Function "INSTR2" DDL CREATE FUNCTION `INSTR2`(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; end if; end if; end if; return icount; end;