GBase 8s 自定义split_part函数

GBase 8s 默认无split_part函数，但可以通过substring_index()函数来达到split_part的功能，具体如下：

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
drop function if exists split_part2;
 
create function split_part2( str_in lvarchar(2048), separator_in char(1), field_in int  )
  returning varchar(255) ;
 
  define str_len int;
  define pos_curr int;
  define count_field   int;
  define pos_char char(1);
 
--set debug file to "/home/gbasedbt/temp/0311/split.deg";
--trace on;
 
  IF field_in <= 0 THEN return null; END IF;
 
  LET count_field   = 0;
  LET str_len = length(str_in);
 
  FOR pos_curr = 1 TO str_len
    LET pos_char = substr(str_in,pos_curr,1);
    IF pos_char = separator_in THEN
      LET count_field = count_field + 1;
    END IF;
  END FOR;
 
  IF field_in > count_field + 1 THEN
    return null;
  ELSE
    return SUBSTRING_INDEX(SUBSTRING_INDEX(str_in,separator_in,field_in),separator_in,-1);
  END iF;
--trace off;
 
end function;

或者

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
create function split_part( str_in lvarchar(2048), separator_in char(1), field_in int  )
returning varchar(255) ;
 
define res varchar(255);
define str_len int;
define pos_curr int;
define substr_start int;
define substr_length int;
define pos_char char(1);
 
IF field_in <= 0 THEN return ; END IF;
 
LET res = '';
LET substr_start = 0;
LET substr_length = 0;
LET str_len = length(str_in);
 
FOR pos_curr = 1 TO str_len
 
   LET pos_char = substr(str_in,pos_curr,1);
   IF pos_char = separator_in THEN
      LET field_in = field_in - 1;
   END IF;
 
   IF field_in = 1 and substr_start = 0 THEN
      LET substr_start = pos_curr + DECODE(pos_char,separator_in,1,0);
   END IF;
 
   IF field_in <= 0 THEN
      LET substr_length = pos_curr;
      EXIT FOR;
   END IF;
 
END FOR;
 
IF substr_length = 0 THEN
   LET substr_length = str_len+1;
END IF;
 
IF substr_start = 0 THEN
   LET substr_start = str_len+1;
END IF;
 
IF substr_length < substr_start THEN
   LET substr_length = 0;
ELSE
   LET substr_length = substr_length - substr_start;
END IF;
 
RETURN NVL(substring ( str_in from substr_start for substr_length ),'');
 
end function;

标签: none

相关文章推荐

添加新评论