我们可以在这里使用LISTAGG()沿着一个子字符串操作：

SELECT LISTAGG(SUBSTR(data, 1, INSTR(data, '-') - 1), ',')
       WITHIN GROUP (ORDER BY TO_NUMBER(SUBSTR(data, INSTR(data, '-') + 1, 3))) countries
FROM yourTable;

一种选择是使用正则表达式函数作为

SELECT LISTAGG(REGEXP_SUBSTR(col,'[^-]+'), ',') AS countries
  FROM t

如果基于这些零lpadded值（001，002 ..）排序很重要，则使用

SELECT LISTAGG(REGEXP_SUBSTR(col,'[^-]+'), ',') AS countries
        WITHIN GROUP 
        (ORDER BY TO_NUMBER(REGEXP_SUBSTR(col,'[^- ]+',1,2))) AS countries
  FROM t

请注意，WITHIN GROUP子句是可选的，并且只有在需要排序时才需要，适用于从19c开始的版本

编辑：如果您的数据是用符合问题显示的换行符分隔的，则可以使用

SELECT LISTAGG( REGEXP_SUBSTR( REGEXP_SUBSTR(col,'[^'||CHR(10)||']+',1,level) ,'[^-]+'), ',' )
         WITHIN GROUP ( ORDER BY TO_NUMBER(REGEXP_SUBSTR(col,'[^- ]+',1,2)) ) AS countries
   FROM t
CONNECT BY level <= REGEXP_COUNT(col,CHR(10))+1

你可以使用REPLACE（）函数从第一个'-'开始删除所有内容，然后使用LISTAGG（）分析函数来创建一个列表。请注意，一列有4000个字符的限制。这是针对四行数据的：

WITH         -- Sample data
    tbl AS
      (
          Select 1 "ID", 'INDIA-001 UNIT1-RUNNING' "TXT" From Dual Union All
          Select 2,      'AUSTRIA-002 UNIT2-RUNNING'     From Dual Union All
          Select 3,      'CHINA-003 UNIT1-RUNNING'       From Dual Union All
          Select 4,      'JAPAN-004 UNIT2-ONHOLD'        From Dual 
      )
--
--  SQL
Select      LISTAGG(REPLACE(TXT, SubStr(TXT, InStr(TXT, '-')), ''), ',') WITHIN GROUP (Order By ID) "TXT"
From        tbl
--
--  R e s u l t:
TXT                         
----------------------------
INDIA,AUSTRIA,CHINA,JAPAN

下方-如果数据在单行/列中，没有换行符：

WITH 
    tbl AS
      (
          Select 1 "ID", 'INDIA-001 UNIT1-RUNNING AUSTRIA-002 UNIT2-RUNNING CHINA-003 UNIT1-RUNNING JAPAN-004 UNIT2-ONHOLD.,'  "TXT"  From Dual 
      )
SELECT LISTAGG(SubStr(TXT, 1, CASE WHEN InStr(TXT, '-') - 1 = -1 THEN 9999999 ELSE InStr(TXT, '-') - 1 END), ',') WITHIN GROUP (Order By 1) "TXT"
FROM  (   Select      l.LVL, 
                      LTRIM(REPLACE(SubStr( t.TXT, 
                                            CASE WHEN l.LVL = 1 THEN 1 ELSE Instr(t.TXT, ' ', 1, (LVL-1)*2) END, 
                                            CASE WHEN l.LVL = 1 THEN Instr(t.TXT, '-', 1, 1) - 1 ELSE 9999999999 END
                                          ), ''))  "TXT"
          From        tbl t
          Inner Join  (  Select LEVEL "LVL" From Dual CONNECT BY LEVEL <= CEIL((Select Length(TXT) - Length(Replace(TXT, ' ', '')) "LN" From tbl)/2)  ) l ON(1 = 1) 
      )

如果有一些换行符、制表符和空格：

WITH 
    tbl AS
      (
          Select 1 "ID", 'INDIA-001 UNIT1-RUNNING 
                          AUSTRIA-002 UNIT2-RUNNING 
                          CHINA-003 UNIT1-RUNNING 
                          JAPAN-004 UNIT2-ONHOLD.,'  "TXT"  From Dual 
      ),
    cte AS (Select REPLACE(REPLACE(TXT, Chr(10), ''), CHR(9), '') "TXT" From tbl )
    
SELECT LISTAGG(
            CASE WHEN InStr(CASE WHEN InStr(SubStr(TXT, 1),' ') > 0 
                                THEN LTRIM(SubStr(  SubStr(TXT, 1), InStr(SubStr(TXT, 1), ' ')))
                            ELSE SubStr(TXT, 1)
                            END, '-') > 0
                THEN SubStr(CASE WHEN InStr(SubStr(TXT, 1),' ') > 0 
                                THEN LTRIM(SubStr(  SubStr(TXT, 1), InStr(SubStr(TXT, 1), ' ')))
                            ELSE SubStr(TXT, 1)
                            END, 1, 
                        InStr(CASE WHEN InStr(SubStr(TXT, 1),' ') > 0 
                                  THEN LTRIM(SubStr(  SubStr(TXT, 1), InStr(SubStr(TXT, 1), ' ')))
                              ELSE SubStr(TXT, 1)
                              END, '-') - 1 )
          ELSE  
                CASE WHEN InStr(SubStr(TXT, 1),' ') > 0 
                    THEN LTRIM(SubStr(  SubStr(TXT, 1), InStr(SubStr(TXT, 1), ' ')))
                ELSE SubStr(TXT, 1)
                END
          END, ',') WITHIN GROUP (Order By 1) "TXT"
FROM  (  Select      l.LVL, 
                      LTRIM(REPLACE(SubStr( t.TXT, 
                                            CASE WHEN l.LVL = 1 THEN 1 ELSE Instr(t.TXT, '-', 1, (LVL-1)*2) END, 
                                            CASE WHEN l.LVL = 1 THEN Instr(t.TXT, '-', 1, 1) - 1 ELSE 9999999999 END
                                          ), ''))  "TXT"
          From        cte t
          Inner Join  (  Select LEVEL "LVL" From Dual CONNECT BY LEVEL <= CEIL((Select Length(TXT) - Length(Replace(TXT, Chr(10), '')) + 1 "LN" From tbl))  ) l ON(1 = 1) 
      )

结果应该是一样的。。