函数：字符串倒叙排列

in_string  要倒叙的字符串 1,2,3

in_delimiter ：分隔符 “，”

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

-- 字符串倒叙排列 例如:1,2,3 变更为 3,2,1

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 := substr(in_string,prev_location+1,deli_location - prev_location - 1)|| ',' || FunctionResult ;

prev_location := deli_location;

end loop;

/*截取最后拼接的分隔符','，即为处理后结果*/

FunctionResult := rtrim(FunctionResult,',');

return(FunctionResult);

end fun_string_tr;