通过 Oracle SQL 查询将行中的列的逗号分隔值拆分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23649813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split comma separated values of a column in row, through Oracle SQL query
提问by vipin.huddar
I have a table like below:
我有一张如下表：
-------------
ID | NAME
-------------
1001 | A,B,C
1002 | D,E,F
1003 | C,E,G
-------------
I want these values to be displayed as:
我希望这些值显示为：
-------------
ID | NAME
-------------
1001 | A
1001 | B
1001 | C
1002 | D
1002 | E
1002 | F
1003 | C
1003 | E
1003 | G
-------------
I tried doing:
我试着做：
select split('A,B,C,D,E,F', ',') from dual; -- WILL RETURN COLLECTION
select column_value
from table (select split('A,B,C,D,E,F', ',') from dual); -- RETURN COLUMN_VALUE
采纳答案by Nishanthi Grashia
Try using below query:
尝试使用以下查询：
WITH T AS (SELECT 'A,B,C,D,E,F' STR FROM DUAL) SELECT
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES FROM T
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T)
Below Query with ID:
在带有 ID 的查询下方：
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
)
SELECT ID,
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES FROM TAB
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM TAB);
EDIT:Try using below query for multiple IDs and multiple separation:
编辑：尝试对多个 ID 和多个分隔使用以下查询：
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
select id, substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
from
( select ',' || STR || ',' as STR, id from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1
order by ID, NAME
回答by Lalit Kumar B
There are multiple options. See Split comma delimited strings in a table in Oracle.
有多种选择。请参阅在 Oracle 中的表中拆分逗号分隔的字符串。
Using REGEXP_SUBSTR:
使用REGEXP_SUBSTR：
SQL> WITH sample_data AS(
2 SELECT 10001 ID, 'A,B,C' str FROM dual UNION ALL
3 SELECT 10002 ID, 'D,E,F' str FROM dual UNION ALL
4 SELECT 10003 ID, 'C,E,G' str FROM dual
5 )
6 -- end of sample_data mimicking real table
7 SELECT distinct id, trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
8 FROM sample_data
9 CONNECT BY LEVEL <= regexp_count(str, ',')+1
10 ORDER BY ID
11 /
ID STR
---------- -----
10001 A
10001 B
10001 C
10002 D
10002 E
10002 F
10003 C
10003 E
10003 G
9 rows selected.
SQL>
Using XMLTABLE:
使用XMLTABLE：
SQL> WITH sample_data AS(
2 SELECT 10001 ID, 'A,B,C' str FROM dual UNION ALL
3 SELECT 10002 ID, 'D,E,F' str FROM dual UNION ALL
4 SELECT 10003 ID, 'C,E,G' str FROM dual
5 )
6 -- end of sample_data mimicking real table
7 SELECT id,
8 trim(COLUMN_VALUE) str
9 FROM sample_data,
10 xmltable(('"'
11 || REPLACE(str, ',', '","')
12 || '"'))
13 /
ID STR
---------- ---
10001 A
10001 B
10001 C
10002 D
10002 E
10002 F
10003 C
10003 E
10003 G
9 rows selected.
回答by KARLOS
i solved similar problem this way...
我以这种方式解决了类似的问题...
select YT.ID,
REPLACE(REGEXP_SUBSTR(','||YT.STR||',',',.*?,',1,lvl.lvl),',','') AS STR
from YOURTABLE YT
join (select level as lvl
from dual
connect by level <= (select max(regexp_count(STR,',')+1) from YOURTABLE)
) lvl on lvl.lvl <= regexp_count(YT.STR,',')+1
回答by Benjamin
I tried the solution of Lalit Kumar B and it worked so far. But with more data I ran into an performance issue (> 60 Rows, >7 Level). Therefore I used a more static variation, I would like to share as alternative.
我尝试了 Lalit Kumar B 的解决方案，到目前为止它有效。但是随着数据的增多，我遇到了性能问题（> 60 行，> 7 级）。因此我使用了一个更静态的变体，我想作为替代分享。
WITH T AS (
SELECT 1001 AS ID, 'A,B,C' AS NAME FROM DUAL
UNION SELECT 1002 AS ID, 'D,E,F' AS NAME FROM DUAL
UNION SELECT 1003 AS ID, 'C,E,G' AS NAME FROM DUAL
) --SELECT * FROM T
SELECT ID as ID,
distinct_column AS NAME
FROM ( SELECT t.ID,
trim(regexp_substr(t.NAME, '[^,]+', 1,1)) AS c1,
trim(regexp_substr(t.NAME, '[^,]+', 1,2)) AS c2,
trim(regexp_substr(t.NAME, '[^,]+', 1,3)) AS c3,
trim(regexp_substr(t.NAME, '[^,]+', 1,4)) AS c4 -- etc.
FROM T )
UNPIVOT ( distinct_column FOR cn IN ( c1, c2, c3, c4 ) )
ID NAME
------ ------
1001 A
1001 B
1001 C
1002 D
1002 E
1002 F
1003 C
1003 E
1003 G
9 Zeilen gew?hlt
回答by Adam
Do not use CONNECT BY or REGEXP which results in a Cartesian product on a complex query. Furthermore the above solutions expect you know the possible results (A,B,C,D,E,F) rather than a list of combinations
不要使用 CONNECT BY 或 REGEXP，这会在复杂查询中产生笛卡尔积。此外，上述解决方案希望您知道可能的结果（A、B、C、D、E、F）而不是组合列表
Use XMLTable:
使用 XMLTable：
SELECT c.fname, c.lname,
trim(COLUMN_VALUE) EMAIL_ADDRESS
FROM
CONTACTS c, CONTACT_STATUS s,
xmltable(('"'
|| REPLACE(EMAIL_ADDRESS, ';', '","')
|| '"'))
where c.status = s.id
The COLUMN_VALUE is a pseudocolumn that belongs to xmltable. This is quick and correct and allows you to reference a column w/o know its values.
COLUMN_VALUE 是属于 xmltable 的伪列。这是快速且正确的，并允许您在不知道其值的情况下引用列。
This takes the column and makes a table of values "item","item2","item3" and automatically joins to its source table (CONTACTS). This was tested on thousands of rows
这将获取该列并创建一个值表“item”、“item2”、“item3”，并自动连接到其源表 (CONTACTS)。这在数千行上进行了测试
Notethe ';' in the xmltable is the separator in the column field.
注意';' 在 xmltable 中是列字段中的分隔符。
回答by i100
You may try something like this:
你可以尝试这样的事情：
CREATE OR REPLACE TYPE "STR_TABLE"
as table of varchar2
create or replace function GetCollection( iStr varchar2, iSplit char default ',' ) return STR_TABLE as
pStr varchar2(4000) := trim(iStr);
rpart varchar(255);
pColl STR_TABLE := STR_TABLE();
begin
while nvl(length(pStr),0) > 0 loop
pos := inStr(pStr, iSplit );
if pos > 0 then
rpart := substr(pStr,1, pos-1);
pStr := substr(pStr,pos+1,length(pStr));
else
rpart := pStr;
pStr := null;
end if;
if rpart is not null then
pColl.Extend;
pColl(pColl.Count) := rpart;
end if;
end loop;
return pColl;
end;
回答by dev-null
this version works also with strings longer than one char:
此版本也适用于长度超过一个字符的字符串：
select regexp_substr('A,B,C,Karl-Heinz,D','[^,]+', 1, level) from dual
connect by regexp_substr('A,B,C,Karl-Heinz,D', '[^,]+', 1, level) is not null;
see How to split comma separated string and pass to IN clause of select statement
