【原创】oracle函数INSTR的MySQL实现-阿里云开发者社区

【原创】oracle函数INSTR的MySQL实现

2017-11-22 1637

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

+关注继续查看

一个迁移项目遇到的，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 ;

本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/1719867，如需转载请自行联系原作者