Oracle SQL：如何提取分隔字符串的最后N个限定符？

Got it, let's tackle this problem. You want to grab the last 5 elements from a comma-separated string in Oracle, and your example of '1,2,3,4,5,6,7' should return '3,4,5,6,7'. Here are two reliable approaches that work directly in Oracle SQL:

Approach 1: Reverse + Regex (Simple & Performant) #

This method leverages reversing the string to make it easy to target the "first" 5 elements (which correspond to the original string's last 5), then reverses back to restore the correct order.

SELECT
  REVERSE(
    REGEXP_SUBSTR(REVERSE(val), '([^,]+,){0,4}[^,]+', 1, 1)
  ) AS last_five_elements
FROM (
  SELECT '1,2,3,4,5,6,7' AS val FROM DUAL
);

How it works: #

1. REVERSE(val) turns '1,2,3,4,5,6,7' into '7,6,5,4,3,2,1'
2. The regex ([^,]+,){0,4}[^,]+ matches up to 5 elements:
   • ([^,]+,){0,4}: 0 to 4 instances of "non-comma characters followed by a comma"
   • [^,]+: The final non-comma element
   • This ensures we capture the first 5 elements in the reversed string (original last 5)
3. REVERSE() again flips the captured substring back to '3,4,5,6,7'

Bonus: If your string has fewer than 5 elements, this will just return the entire string (no errors).

Approach 2: Split into Rows + Aggregate (Flexible) #

If you need more control (like filtering elements or transforming them before aggregating), splitting the string into rows with XMLTable then selecting the last 5 is a great option.

WITH split_elements AS (
  SELECT
    element,
    -- Assign descending row numbers to prioritize the last elements
    ROW_NUMBER() OVER(ORDER BY rownum DESC) AS rn
  FROM XMLTABLE(
    'tokenize($val, ",")' 
    PASSING val AS val 
    COLUMNS element VARCHAR2(100) PATH '.'
  )
)
SELECT
  LISTAGG(element, ',') WITHIN GROUP(ORDER BY rn) AS last_five_elements
FROM split_elements
WHERE rn <= 5;

How it works: #

1. XMLTable uses the tokenize XPath function to split the comma-separated string into individual rows
2. ROW_NUMBER() OVER(ORDER BY rownum DESC) gives the last element row number 1, second-last 2, etc.
3. We filter for rows where rn <=5 to get the last 5 elements
4. LISTAGG stitches the elements back together in the original order by sorting on rn

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