MySQL如何实现oracle函数INSTR的功能
下文我给大家简单讲讲关于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的功能这篇文章怎么样,是否有所收获。如果想要了解更多相关,可以继续关注我们的行业资讯板块。
当前文章:MySQL如何实现oracle函数INSTR的功能
网址分享:http://pwwzsj.com/article/gjjosd.html