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