使用“正則表達式”在Oracle中用“，”分隔特定列的值

我有一個具有以下結構的表：

create table CUSTOMER_INFO
(
 cust_id      NUMBER,
 cust_name    VARCHAR2(50),
 cust_address VARCHAR2(50)
)

範例數據：

cust_id   |  cust_name |  cust_address
------------------------------------------
 1       |     A      |  Tehran,Tehran  
 2       |     B      |  Rasht,Tehran    
 3       |     C      |  Minsk,Tehran  

我想要這個輸出：

cust_id   |  cust_name |  cust_address
------------------------------------------
 1       |     A      |  Tehran
 2       |     B      |  Tehran    
 2       |     B      |  Rasht
 3       |     C      |  Minsk
 3       |     C      |  Tehran

到目前為止我寫的是這樣的：

select distinct cust_id, 
               cust_name, 
               address
 from (select distinct cust_id,
                       cust_name,
                       regexp_substr(cust_address, '[^,]+', 1, level) address
         from customer_info 
       connect by regexp_substr(cust_address, '[^,]+', 1, level) is not null)
order by cust_id 

我想知道是否有更好的方法來編寫這個查詢，這個解決方案會產生錯誤的結果嗎？提前致謝

這種方法可確保您不會像查詢那樣得到太多重複項（因此您使用distinct第 4 行的 in 刪除了它們；沒有它，您將得到 12 行結果）：

SQL> with customer_info (cust_id, cust_name, cust_address) as
 2    (select 1, 'A', 'Tehran,Tehran' from dual union all
 3     select 2, 'B', 'Rasht,Tehran'  from dual union all
 4     select 3, 'C', 'Minsk,Tehran'  from dual
 5    )
 6  select --distinct      --> use DISTINCT to get result you want
 7         cust_id,
 8         cust_name,
 9         regexp_substr(cust_address, '[^,]+', 1, column_value) cust_address
10  from customer_info cross join
11    table(cast(multiset(select level from dual
12                        connect by level <= regexp_count(cust_address, ',') + 1
13                       ) as sys.odcinumberlist))
14  order by cust_id;

  CUST_ID C CUST_ADDRESS
---------- - ----------------------------------------------------
        1 A Tehran
        1 A Tehran
        2 B Rasht
        2 B Tehran
        3 C Minsk
        3 C Tehran

6 rows selected.

SQL>

但是，由於您只想擁有一種1-A-Tehran組合，distinct因此必須取消註釋第 #6 行。

引用自：https://dba.stackexchange.com/questions/274593