Convert string with groups of numbers to a comma separated string with single numbers in PL/SQL

I gave it a try and could achieve something like,

CREATE OR REPLACE FUNCTION get_series_of_numbers(p_input_string IN VARCHAR2) 
RETURN VARCHAR2 IS
   lo_start        NUMBER;
   lo_end          NUMBER;
   lo_final_string VARCHAR2(4000);
   --a convinient method to get the series for a strig like '1-3' or 9-15'
   FUNCTION get_series(p_series_string VARCHAR2) RETURN VARCHAR2 IS
      lo_string_to_return VARCHAR2(4000);
   BEGIN
      IF instr(p_series_string
              ,'-') > 0
      THEN
         lo_start := to_number(substr(p_series_string
                                     ,1
                                     ,instr(p_series_string
                                           ,'-') - 1));
         lo_end   := to_number(substr(p_series_string
                                     ,instr(p_series_string
                                           ,'-') + 1));
         --query to generate a series of numbers between a start and end point and then concatenate all with ','
         SELECT listagg(actual_numbers
                       ,',') within GROUP(ORDER BY actual_numbers)
         INTO   lo_string_to_return
         FROM   (SELECT LEVEL actual_numbers FROM dual WHERE LEVEL >= lo_start CONNECT BY LEVEL <= lo_end);
      ELSE
         lo_string_to_return := p_series_string;
      END IF;
      RETURN lo_string_to_return;
   END;
BEGIN
   --this loop is to get all the elements in the string separated by ',' as column 
   --so that we can loop over all   
   FOR i IN (SELECT regexp_substr(str
                                 ,'[^,]+'
                                 ,1
                                 ,rownum) split
             FROM   (SELECT p_input_string str FROM dual)
             CONNECT BY LEVEL <= length(regexp_replace(str
                                                      ,'[^,]+')) + 1)
   LOOP
      IF lo_final_string IS NOT NULL
      THEN
         lo_final_string := lo_final_string || ',' || get_series(i.split);
      ELSE
         lo_final_string := get_series(i.split);
      END IF;
   END LOOP;
   RETURN lo_final_string;
END get_series_of_numbers;

Some test results:

DECLARE
  input_string VARCHAR2(4000) := '1,2,8-3,4';
  result_string VARCHAR2(4000);
begin
  dbms_output.put_line('Input string is: '||input_string);
  result_string := get_series_of_numbers(p_input_string => input_string);
  dbms_output.put_line('Output string is: '||result_string);
end;
/*
Input string is: 1-3,4,9,11-15
Output string is: 1,2,3,4,9,11,12,13,14,15

Input string is: 1-3,4-6,9-10,11-15
Output string is: 1,2,3,4,5,6,9,10,11,12,13,14,15

Input string is: 1,2,3,4
Output string is: 1,2,3,4

Input string is: 1,2,3-8,4
Output string is: 1,2,3,4,5,6,7,8,4

--a negative case
Input string is: 1,2,8-3,4
Output string is: 1,2,,4
*/

Hope it gives an idea about the requirement which could be further optimized to handle corner cases or anything which is not yet covered. Cheers!!

Consider this different approach breaking the steps down using CTE's. See the comments within. One could combine some of these but keeping steps separated keeps it simpler. It could and should be made into a procedure or function for re-usability too.

This also handles NULL list elements and numbers out of numeric ordering will be sorted numerically in the output. Try with data like '1-3,17-20,4,9,,11-15'. Always expect the unexpected!

-- tbl_orig only creates a source for the original data
WITH tbl_orig(orig_str) AS (
  SELECT '1-3,4,9,11-15' FROM dual
),
-- tbl_rows then contains that data split into rows on
-- the comma
tbl_rows(str_element) AS (
  SELECT REGEXP_SUBSTR(orig_str, '(.*?)(,|$)', 1, LEVEL, NULL, 1)
  FROM tbl_orig
  CONNECT BY LEVEL <= REGEXP_COUNT(orig_str, ',')+1
),
-- Next look at those rows and if does not contain a hyphen just keep it,
-- else use an inline view to expand the range using listagg and regex's to
-- get the start and end of the range
tbl_expanded(str_expanded) AS (
SELECT  
  CASE INSTR(str_element, '-', 1)
    WHEN 0 THEN str_element
    ELSE (SELECT LISTAGG(n, ',') WITHIN GROUP (ORDER BY n)
          FROM (SELECT ROWNUM n FROM dual CONNECT BY LEVEL <= REGEXP_SUBSTR(str_element, '\d+-(\d+)', 1, 1, NULL, 1))
          WHERE n >= REGEXP_SUBSTR(str_element, '(\d+)-\d+', 1, 1, NULL, 1)
         )
  END AS str_expanded
FROM tbl_rows
)
-- Lastly put it all back together, but order by the numeric value of the first part of
-- the character strings.  
SELECT LISTAGG(str_expanded, ',') WITHIN GROUP (ORDER BY TO_NUMBER(REGEXP_REPLACE(str_expanded, '(\d+).*', '\1')))
  as fullrange
FROM tbl_expanded;


FULLRANGE                                                                       
--------------------------------------------------------------------------------
1,2,3,4,9,11,12,13,14,15