【原创】oracle函数INSTR的MySQL实现
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【原创】oracle函数INSTR的MySQL实现,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2802字,纯文字阅读大概需要5分钟。
内容图文
![【原创】oracle函数INSTR的MySQL实现](/upload/InfoBanner/zyjiaocheng/1315/8c959dca5aac4269a8b0cde1d7aaa90d.jpg)
一个迁移项目遇到的,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 ;
本文出自 “上帝,咱们不见不散!” 博客,请务必保留此出处http://yueliangdao0608.blog.51cto.com/397025/1719867
原文:http://yueliangdao0608.blog.51cto.com/397025/1719867
内容总结
以上是互联网集市为您收集整理的【原创】oracle函数INSTR的MySQL实现全部内容,希望文章能够帮你解决【原创】oracle函数INSTR的MySQL实现所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。