Oracle中拆分含逗号与连字符范围的字符串为多行的方法

Great question! Your comma-split logic works perfectly for individual values, but ranges need a different approach since we have to generate a sequence between the start and end numbers. Here's how to handle both cases, whether you're working with a single string or a table column.

Step 1: Splitting a Single Range String #

For a range like 'A1-A4', we need to extract the prefix, start number, end number, then generate each value in the sequence:

SELECT 
  -- Extract the non-numeric prefix (e.g., 'A' from 'A1-A4')
  REGEXP_REPLACE('A1-A4', '(\D+)(\d+)-\D*(\d+)', '\1') ||
  -- Generate numbers from start to end
  (TO_NUMBER(REGEXP_SUBSTR('A1-A4', '\d+', 1, 1)) + LEVEL - 1) AS split_value
FROM dual
-- Connect until we reach the end number
CONNECT BY LEVEL <= (
  TO_NUMBER(REGEXP_SUBSTR('A1-A4', '\d+', 1, 2)) - 
  TO_NUMBER(REGEXP_SUBSTR('A1-A4', '\d+', 1, 1)) + 1
);

This query will return 4 rows: A1, A2, A3, A4.

Step 2: Handling Mixed Cases (Commas + Ranges) in a Table #

If your table has rows with both comma-separated values and ranges, we can use nested CTEs to first split commas, then process ranges:

Assume your table is named your_table with columns id (to track original rows) and seq_data (the column with your sequence values):

WITH comma_split AS (
  -- First split comma-separated values into individual entries
  SELECT 
    id,
    TRIM(REGEXP_SUBSTR(seq_data, '[^,]+', 1, LEVEL)) AS entry
  FROM your_table
  CONNECT BY 
    REGEXP_SUBSTR(seq_data, '[^,]+', 1, LEVEL) IS NOT NULL
    -- Prevent duplicate rows across original table entries
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL
),
range_split AS (
  -- Split range entries into individual values; keep non-ranges as-is
  SELECT 
    id,
    CASE 
      WHEN entry LIKE '%-%' THEN
        REGEXP_REPLACE(entry, '(\D+)(\d+)-\D*(\d+)', '\1') ||
        (TO_NUMBER(REGEXP_SUBSTR(entry, '\d+', 1, 1)) + LEVEL - 1)
      ELSE entry
    END AS final_value
  FROM comma_split
  CONNECT BY 
    -- Generate rows only for ranges (1 row for non-ranges)
    CASE 
      WHEN entry LIKE '%-%' THEN
        LEVEL <= (
          TO_NUMBER(REGEXP_SUBSTR(entry, '\d+', 1, 2)) - 
          TO_NUMBER(REGEXP_SUBSTR(entry, '\d+', 1, 1)) + 1
        )
      ELSE LEVEL = 1
    END
    -- Prevent duplicates again
    AND PRIOR id = id
    AND PRIOR entry = entry
    AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT id, final_value FROM range_split;

Key Features: #

• Trimming Spaces: The TRIM() function handles any spaces after commas (e.g., 'A1, A2, A4' becomes clean entries).
• Flexible Prefixes: Works with multi-letter prefixes (like 'AB1-AB5' → AB1, AB2, etc.) or no prefixes (like '1-4' → 1, 2, etc.).
• Edge Cases: Handles ranges with identical start/end numbers (e.g., 'A5-A5' returns just A5).

Example Output #

If your table has these rows:

The query will return:

内容的提问来源于stack exchange，提问作者Muhammad Asim