SQLize | PHPize | SQLtest

WITH input AS ( SELECT '302(01:03), 502(05:09)' AS str ), split_elements AS ( SELECT TRIM(REGEXP_SUBSTR(str, '[^,]+', 1, level)) AS element FROM input CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, level) IS NOT NULL ), parsed AS ( SELECT SUBSTR(element, 1, 3) AS prefix, TO_NUMBER(REGEXP_SUBSTR(element, '\((\d+):', 1, 1, NULL, 1)) AS range_start, TO_NUMBER(REGEXP_SUBSTR(element, ':(\d+)\)', 1, 1, NULL, 1)) AS range_end FROM split_elements ), numbers AS ( SELECT prefix, range_start + level - 1 AS suffix FROM parsed CONNECT BY level <= range_end - range_start + 1 AND PRIOR prefix = prefix AND PRIOR SYS_GUID() IS NOT NULL ) SELECT DISTINCT prefix || LPAD(suffix, 2, '0') AS account_number FROM numbers ORDER BY account_number;