去掉字符串中逗号间重复的字符,并按升序排列

FUNCTION getTESTCOUNT(acc_str IN VARCHAR2) RETURN VARCHAR2 IS
v_string VARCHAR2(1000);
v_count NUMBER;
TYPE test_value IS TABLE OF CHAR(4) INDEX BY PLS_INTEGER;
v_test_value test_value;
v_value VARCHAR2(10);
BEGIN
SELECT col
INTO v_string
FROM ( --将字符串中的所有序号进行过滤，使没用重复
SELECT sys_connect_by_path(col, ',') || ',' col, LEVEL
FROM (SELECT col, row_number() over(ORDER BY col) rn
FROM (SELECT DISTINCT substr(col,
instr(col, ',', 1, rownum) + 1,
instr(col, ',', 1, rownum + 1) -
instr(col, ',', 1, rownum) - 1) col
FROM (SELECT acc_str col FROM dual)
CONNECT BY rownum <
length(translate(col, ',' || col, ','))))
CONNECT BY PRIOR rn = rn - 1
ORDER BY LEVEL DESC)
WHERE rownum = 1;
v_count := lengthb(translate(v_string, ',' || v_string, '~')) - 1; --求出字符串中逗号的个数
FOR v_cur IN 1 .. v_count LOOP
--将字符串中的序号取出，分别数组v_test_value
v_test_value(v_cur) := substr(v_string, 2, instr(v_string, ',', 1, 2) - 2);
v_string := substr(v_string,
instr(v_string, ',', 1, 2),
(length(v_string) + 1 - instr(v_string, ',', 1, 2)));
END LOOP;
v_value := NULL;
FOR r_cur IN 1 .. v_count - 1 LOOP
--将数组v_test_value从小到大排序
FOR t_vur IN r_cur + 1 .. v_count LOOP
IF to_number(TRIM(v_test_value(r_cur))) >
to_number(TRIM(v_test_value(t_vur))) THEN
v_value := v_test_value(r_cur);
v_test_value(r_cur) := v_test_value(t_vur);
v_test_value(t_vur) := v_value;
END IF;

END LOOP;

END LOOP;
v_string := ',';
FOR v_cur IN 1 .. v_Count LOOP
--将排序好的数组重新变为字符串不过之间用逗号隔开
v_string := v_string || TRIM(v_test_value(v_cur)) || ',';
END LOOP;
RETURN TRIM(v_string);
END getTESTCOUNT;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7185996/viewspace-135975/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7185996/viewspace-135975/