GBase 8s 自定义split_part函数

该函数的功能：以第二个参数separator_in分隔第一个参数str_in，返回第三个参数field_in指定字段。

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);

  IF field_in <= 0 THEN return null; END IF;

  LET count_field   = 1;
  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 THEN
    return null;
  ELSE
    return SUBSTRING_INDEX(SUBSTRING_INDEX(str_in,separator_in,field_in),separator_in,-1);
  END iF;

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;                                                                                                                                                           

示例： 以，分隔'aa,bb,cc,dd'，返回第三个字段。

select split_part2('aa,bb,cc,dd',',',3) from dual; 

结果： cc