Search This Blog

Wednesday, May 9, 2018

Sort Number's String Order by Number by Oracle PL/SQL Function STRING_SORT (p_str VARCHAR2 )

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

PDF to Text Covert by Oracle Apex

Here are the steps to convert and get the character into a region by Oracle Apex. Step1.  Create a page and Copy-Pest the below code into Pa...