Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4149655
  • 博文数量: 240
  • 博客积分: 11504
  • 博客等级: 上将
  • 技术积分: 4277
  • 用 户 组: 普通用户
  • 注册时间: 2006-12-28 14:24
文章分类

全部博文(240)

分类: Mysql/postgreSQL

2015-12-05 15:35:41

一个迁移项目遇到的,MySQL的instr函数只能查找子串是否在父串中,没法按照出现的次数进行查找。 
这里我自己写了一个,以便迁移。当然我这里仅仅针对的是迁移,可能没有完全实现原有函数的细节。


Oracle 里用了几次如下的调用,



点击(此处)折叠或打开

  1. SQL> select instr('This is belong to you, but not to me.','to',1,1) as pos from dual;


  2.                  POS
  3. --------------------
  4.                   16


  5. 已用时间: 00: 00: 00.00
  6. SQL> select instr('This is belong to you, but not to me.','to',1,2) as pos from dual;


  7.                  POS
  8. --------------------
  9.                   32


  10. 已用时间: 00: 00: 00.00
  11. SQL> select instr('This is belong to you, but not to me.','belong',-1,1) as pos from dual;


  12.                  POS
  13. --------------------
  14.                    9


  15. 已用时间: 00: 00: 00.00
  16. SQL> select instr('This is belong to you, but not to me.','belong',-1,2) as pos from dual;


  17.                  POS
  18. --------------------
  19.                   0


  20. 已用时间: 00: 00: 00.00





MySQL里效果如下,





点击(此处)折叠或打开

  1. mysql> select func_instr_oracle('This is belong to you, but not to me.','to',1,1) as pos;
  2. +------+
  3. | pos |
  4. +------+
  5. | 16 |
  6. +------+
  7. 1 row in set (0.00 sec)


  8. mysql> select func_instr_oracle('This is belong to you, but not to me.','to',1,2) as pos;
  9. +------+
  10. | pos |
  11. +------+
  12. | 32 |
  13. +------+
  14. 1 row in set (0.00 sec)


  15. mysql> select func_instr_oracle('This is belong to you, but not to me.','belong',-1,1) as pos;
  16. +------+
  17. | pos |
  18. +------+
  19. | 9 |
  20. +------+
  21. 1 row in set (0.00 sec)


  22. mysql> select func_instr_oracle('This is belong to you, but not to me.','belong',-1,2) as pos;
  23. +------+
  24. | pos |
  25. +------+
  26. | 0 |
  27. +------+
  28. 1 row in set (0.00 sec)




附上函数func_instr_oracle的代码:


点击(此处)折叠或打开

  1. DELIMITER $$


  2. USE `oracle12c`$$


  3. DROP FUNCTION IF EXISTS `func_instr_oracle`$$


  4. CREATE DEFINER=`root`@`localhost` FUNCTION `func_instr_oracle`(
  5.     f_str VARCHAR(1000), -- Parameter 1
  6.     f_substr VARCHAR(100), -- Parameter 2
  7.     f_str_pos INT, -- Postion
  8.     f_count INT UNSIGNED -- Times
  9.     ) RETURNS INT(10) UNSIGNED
  10. BEGIN
  11.       -- Created by ytt. Simulating Oracle instr function.
  12.       -- Date 2015/12/5.
  13.       DECLARE i INT DEFAULT 0; -- Postion iterator
  14.       DECLARE j INT DEFAULT 0; -- Times compare.
  15.       DECLARE v_substr_len INT UNSIGNED DEFAULT 0; -- Length for Parameter 1.
  16.       DECLARE v_str_len INT UNSIGNED DEFAULT 0; -- Length for Parameter 2.
  17.       SET v_str_len = LENGTH(f_str);
  18.       SET v_substr_len = LENGTH(f_substr);
  19.       -- Unsigned.
  20.       IF f_str_pos > 0 THEN
  21.         SET i = f_str_pos;
  22.         SET j = 0;
  23.         WHILE i <= v_str_len
  24.         DO
  25.           IF INSTR(LEFT(SUBSTR(f_str,i),v_substr_len),f_substr) > 0 THEN
  26.             SET j = j + 1;
  27.             IF j = f_count THEN
  28.               RETURN i;
  29.             END IF;
  30.           END IF;
  31.           SET i = i + 1;
  32.         END WHILE;
  33.       -- Signed.
  34.       ELSEIF f_str_pos <0 THEN
  35.         SET i = v_str_len + f_str_pos+1;
  36.         SET j = 0;
  37.         WHILE i <= v_str_len AND i > 0
  38.         DO
  39.           IF INSTR(RIGHT(SUBSTR(f_str,1,i),v_substr_len),f_substr) > 0 THEN
  40.             SET j = j + 1;
  41.             IF j = f_count THEN
  42.               RETURN i - v_substr_len + 1;
  43.             END IF;
  44.           END IF;
  45.           SET i = i - 1;
  46.         END WHILE;
  47.       -- Equal to 0.
  48.       ELSE
  49.         RETURN 0;
  50.       END IF;
  51.       RETURN 0;
  52.     END$$


  53. DELIMITER ;




阅读(9753) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~