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
Muhammad Abdul Qaium is a Database Engineer/Oracle Apex Developer/BI Developer (in Connecticut, USA) who is an Oracle Certified Cloud Architect Professional, OCI Autonomous DB specialist as well as Oracle Business Intelligence Foundation Suite 11g Certified Implementation Specialist with extensive expertise in Database design , PL/SQL, Oracle Apex, Microsoft SSIS, ETL, Power BI, Qlik Sense, BI Publisher, Jasper Report, OBIEE. Contact: qaiuminfo@gmail.com
Search This Blog
Subscribe to:
Post Comments (Atom)
Java to Oracle database Connection Using JDBC
Oracle database. // Java Program to Establish Connection // in JDBC with Oracle Database // Importing database import java.sql.*; // Import...
-
Search by keyword from Oracle Database Objects. select owner, type, name, line, text from dba_source where 1 = 1 and text like '%TEXT Y...
-
We Can Export Application Components individually from Oracle Apex. Application > Shared Components > Export Application Compo...
No comments:
Post a Comment