您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
下文我给大家简单讲讲关于MySQL如何实现oracle函数INSTR的功能,大家之前了解过相关类似主题内容吗?感兴趣的话就一起来看看这篇文章吧,相信看完MySQL如何实现oracle函数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的功能这篇文章怎么样,是否有所收获。如果想要了解更多相关,可以继续关注我们的行业资讯板块。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。