Oracle
使用“正則表達式”在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 行。