create or replace function fun_string_tr(in_string in varchar2,in_delimiter in varchar2) return varchar2
is
FunctionResult varchar2(512) default '';
string_adjusted varchar2(256);
deli_location number := 0;
prev_location number := 0;
begin
if in_string is null or in_delimiter is null then
return '';
end if;
/*末尾拼接一个分隔符,方便数据处理*/
string_adjusted := in_string || in_delimiter;
loop
/*获取分隔位置*/
deli_location := instr(string_adjusted,in_delimiter,deli_location+1);
exit when deli_location = 0;
/*从1开始截取,截取长度为 deli_location - prev_location - 1 */
FunctionResult := FunctionResult || FUN_TR_ID2NAME(substr(in_string,prev_location+1,deli_location - prev_location - 1)) || ',';
prev_location := deli_location;
end loop;
/*截取最后拼接的分隔符',',即为处理后结果*/
FunctionResult := rtrim(FunctionResult,',');
return(FunctionResult);
end fun_string_tr;