Shulou(Shulou.com)06/01 Report--

Let me tell you a little bit about how MySQL implements the function of oracle function INSTR. Have you ever heard of similar topics before? If you are interested, let's take a look at this article. I believe it will be more or less helpful for everyone to read how MySQL implements the function INSTR of oracle.

The following calls are used several times in Oracle

SQL > select instr ('This is belong to you, but not to me.','to',1,1) as pos from dual POS-16 elapsed time: 00: 00: 00.00SQL > select instr ('This is belong to you, but not to me.','to',1,2) as pos from dual POS-32 elapsed 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 elapsed 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 elapsed time: 00: 00: 00.00

The effect in MySQL is as follows

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)

Attach the code for the function func_instr_oracle:

DELIMITER $$USE `oracle12c` $DROP FUNCTION IF EXISTS `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 = fancistrampoup; SET j = 0; WHILE I 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 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

What do you think of this article about how MySQL implements the function of oracle function INSTR, and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.