GBase 8s 自定义split_part函数

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

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;                                                                                                                                                         

或者

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

添加新评论