一个迁移项目遇到的，MySQL的instr函数只能查找子串是否在父串中，没法按照出现的次数进行查找。
这里我自己写了一个，以便迁移。当然我这里仅仅针对的是迁移，可能没有完全实现原有函数的细节。
Oracle 里用了几次如下的调用，
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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里效果如下，
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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的代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
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
，如需转载请自行联系原作者
