如何用REGEXP_SUBSTR提取斜杠左侧递进前缀数据？

Got it, let's break down how to use REGEXP_SUBSTR to pull those progressive prefixes from your path-style strings (like abc/ab/123/zzz). The goal is to get abc, abc/ab, and abc/ab/123—here's how to do it, both for individual prefixes and dynamically generating all needed results.

Individual Prefix Extraction #

If you need to fetch each prefix explicitly, you can use targeted regex patterns for each case:

1. Extract the first segment (abc) #

Use a regex that matches from the start of the string up to the first slash (or end of the segment if there's no slash):

SELECT REGEXP_SUBSTR('abc/ab/123/zzz', '^[^/]+') FROM DUAL; -- Output: abc

• ^: Anchors the match to the start of the string (ensures we don't match mid-string segments)
• [^/]+: Matches one or more characters that are not a slash (grabs the first full segment)

2. Extract the first two segments (abc/ab) #

Extend the regex to include the second segment after the first slash:

SELECT REGEXP_SUBSTR('abc/ab/123/zzz', '^[^/]+/[^/]+') FROM DUAL; -- Output: abc/ab

• This pattern combines two [^/]+ segments separated by a slash, anchored to the start.

3. Extract the first three segments (abc/ab/123) #

Add a third segment using a repeated group pattern for cleaner syntax (or just extend the previous pattern):

SELECT REGEXP_SUBSTR('abc/ab/123/zzz', '^([^/]+/){2}[^/]+') FROM DUAL; -- Output: abc/ab/123

• ([^/]+/){2}: Repeats the "segment + slash" pattern exactly 2 times (covers abc/ and ab/)
• The final [^/]+ grabs the third segment (123) without the trailing slash.

Dynamically Generate All Progressive Prefixes #

If you need to automatically generate all prefixes (without hardcoding each one), combine REGEXP_SUBSTR with a recursive/connect-by clause (works in Oracle; adjust for other databases like MySQL with recursive CTEs):

SELECT DISTINCT
  REGEXP_SUBSTR(t.path_col, '^([^/]+/){0,' || (LEVEL - 1) || '}[^/]+') AS progressive_prefix
FROM your_table t
-- Limit to prefixes excluding the final segment (since you don't want `abc/ab/123/zzz`)
CONNECT BY LEVEL <= REGEXP_COUNT(t.path_col, '/')
AND PRIOR t.path_col = t.path_col
AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY progressive_prefix;

• REGEXP_COUNT(t.path_col, '/'): Counts the number of slashes (gives us the number of prefixes we need—3 for your example)
• LEVEL: Iterates from 1 to the number of slashes, generating each prefix incrementally
• The regex adjusts dynamically with LEVEL to build longer prefixes each time.

Notes for Other Databases #

If you're using MySQL instead of Oracle, the core regex logic stays the same, but you'd use a recursive CTE instead of CONNECT BY:

WITH RECURSIVE prefixes AS (
  SELECT
    path_col,
    1 AS level_num,
    REGEXP_SUBSTR(path_col, '^[^/]+') AS progressive_prefix
  FROM your_table
  UNION ALL
  SELECT
    p.path_col,
    p.level_num + 1,
    REGEXP_SUBSTR(p.path_col, '^([^/]+/){' || (p.level_num) || '}[^/]+')
  FROM prefixes p
  WHERE p.level_num < REGEXP_COUNT(p.path_col, '/')
)
SELECT DISTINCT progressive_prefix FROM prefixes ORDER BY progressive_prefix;

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