Sort a string and separate numbers with ( ; ) contain with numbers separated ( , ) .
Example:
Input: "1,9,12,45,14,98,12,445,2"
Output: "1;2;9;12;12;14;45;98;445"
Or
Input: "1622,49,454,458748,500/2,500/1,25/3,25/4,25/5,1"
Output: "1;25/3;25/4;25/5;49;454;500/1;500/2;1622;458748"
You can do this Sort using this function:
CREATE OR REPLACE FUNCTION STRING_SORT (p_str VARCHAR2)
--Muhammad Abdul Qaium
RETURN VARCHAR2
IS
v_1 VARCHAR2 (500);
v_2 VARCHAR2 (500);
BEGIN
FOR i
IN ( SELECT REGEXP_SUBSTR (p_str,
'[^,;&]+',
1,
LEVEL) D
FROM DUAL
CONNECT BY REGEXP_SUBSTR (p_str,
'[^,;&]+',
1,
LEVEL)
IS NOT NULL
ORDER BY TO_NUMBER (
DECODE (SUBSTR (D, 0, INSTR (D, '/') - 1),
NULL, D,
SUBSTR (D, 0, INSTR (D, '/') - 1))),
SUBSTR (D, INSTR (D, '/')) ASC)
LOOP
v_1 := v_1 || ';' || I.D;
END LOOP;
RETURN SUBSTR (v_1, 2);
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
'You have must use (,) or (;) to separate !');
-- DBMS_OUTPUT.put_line (SUBSTR (v_1, 2));
END;
/
Thank You
No comments:
Post a Comment