将字符串拆分为行 Oracle SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26407538/
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 String into rows Oracle SQL
提问by Grant McKinnon
After searching the forums I have come up with the following but its not working :/
在搜索论坛后，我想出了以下内容，但它不起作用：/
I have a table with the following;
我有一张桌子，里面有以下内容；
ID | Strings
123| abc fgh dwd
243| dfs dfd dfg
353| dfs
424| dfd dfw
523|
.
.
.
Please not that there is around 20,000 rows my other option is to write a stored procedure to do this ...Basically I need to split the strings up so there is a row for each one like this
请注意大约有 20,000 行，我的另一个选择是编写一个存储过程来执行此操作...基本上我需要将字符串拆分，这样每个字符串都有一行
ID | Strings
123| abc
123| fgh
123| dwd
243| dfs
and so on...
等等...
this is what I have.
这就是我所拥有的。
create table Temp AS
SELECT ID, strings
From mytable;
SELECT DISTINCT ID, trim(regexp_substr(str, '[^ ]+', 1, level)) str
FROM (SELECT ID, strings str FROM temp) t
CONNECT BY instr(str, ' ', 1, level -1) >0
ORDER BY ID;
Any help is appreciated
任何帮助表示赞赏
回答by Sylvain Leroux
This should do the trick:
这应该可以解决问题：
SELECT DISTINCT ID, regexp_substr("Strings", '[^ ]+', 1, LEVEL)
FROM T
CONNECT BY regexp_substr("Strings", '[^ ]+', 1, LEVEL) IS NOT NULL
ORDER BY ID;
Notice how I used regexp_substrin the connect by clause too. This is to deal with the case of multiple spaces.
请注意我regexp_substr在 connect by 子句中的使用方式。这是为了处理多个空格的情况。
If you have a predictable upper bound on the number of items per line, it might worth comparing the performances of the recursive query above with a simple CROSS JOIN:
如果您对每行的项目数有一个可预测的上限，则可能值得将上述递归查询的性能与简单的进行比较CROSS JOIN：
WITH N as (SELECT LEVEL POS FROM DUAL CONNECT BY LEVEL < 10)
-- ^^
-- up to 10 substrings
SELECT ID, regexp_substr("Strings", '[^ ]+', 1, POS)
FROM T CROSS JOIN N
WHERE regexp_substr("Strings", '[^ ]+', 1, POS) IS NOT NULL
ORDER BY ID;
See http://sqlfiddle.com/#!4/444e3/1for a live demo
有关现场演示，请参阅http://sqlfiddle.com/#!4/444e3/1
回答by Lalit Kumar B
A more flexible and better solution which:
一个更灵活和更好的解决方案：
- doesn't depend upon the predictability of the number of items per line.
- doesn't depend on the ID column, the solution gives correct result irrespective of the number of column.
- doesn't even depend upon the DISTINCTkeyword.
- 不依赖于每行项目数的可预测性。
- 不依赖于 ID 列，无论列数如何，该解决方案都会给出正确的结果。
- 甚至不依赖于DISTINCT关键字。
There are other examples using XMLTABLEand MODEL clause, please read Split comma delimited strings in a table.
还有其他使用XMLTABLE和MODEL 子句的示例，请阅读在表中拆分逗号分隔的字符串。
For example,
例如，
Without ID column:
没有 ID 列：
SQL> WITH T AS
2 (SELECT 'abc fgh dwd' AS text FROM dual
3 UNION
4 SELECT 'dfs dfd dfg' AS text FROM dual
5 UNION
6 SELECT 'dfs' AS text FROM Dual
7 UNION
8 SELECT 'dfd dfw' AS text FROM dual
9 )
10 SELECT trim(regexp_substr(t.text, '[^ ]+', 1, lines.column_value)) text
11 FROM t,
12 TABLE (CAST (MULTISET
13 (SELECT LEVEL FROM dual CONNECT BY instr(t.text, ' ', 1, LEVEL - 1) > 0
14 ) AS sys.odciNumberList )) lines
15 /
TEXT
-----------
abc
fgh
dwd
dfd
dfw
dfs
dfs
dfd
dfg
9 rows selected.
With ID column:
带 ID 列：
SQL> WITH T AS
2 (SELECT 123 AS id, 'abc fgh dwd' AS text FROM dual
3 UNION
4 SELECT 243 AS id, 'dfs dfd dfg' AS text FROM dual
5 UNION
6 SELECT 353 AS Id, 'dfs' AS text FROM Dual
7 UNION
8 SELECT 424 AS id, 'dfd dfw' AS text FROM dual
9 )
10 SELECT id, trim(regexp_substr(t.text, '[^ ]+', 1, lines.column_value)) text
11 FROM t,
12 TABLE (CAST (MULTISET
13 (SELECT LEVEL FROM dual CONNECT BY instr(t.text, ' ', 1, LEVEL - 1) > 0
14 ) AS sys.odciNumberList )) lines
15 ORDER BY id
16 /
ID TEXT
---------- -----------
123 abc
123 fgh
123 dwd
243 dfs
243 dfd
243 dfg
353 dfs
424 dfd
424 dfw
9 rows selected.
SQL>
回答by Steve
With T As
(select 123 as id, 'abc fgh dwd' as strings from dual
union
select 243 as id, 'dfs dfd dfg' as strings from dual
union
Select 353 As Id, 'dfs' As Strings From Dual
union
select 424 as id, 'dfd dfw' as strings from dual
)
select distinct id, REGEXP_SUBSTR (Replace(Strings, ' ', ','), '[^,]+', 1, level) as Strings
from t
Connect By Level <= Length(Regexp_Replace(Replace(Strings, ' ', ','),'[^,]*'))+1
order by id, strings;
**********OUTPUT*************
ID STRINGS
---------- -----------
123 abc
123 dwd
123 fgh
243 dfd
243 dfg
243 dfs
353 dfs
424 dfd
424 dfw
9 rows selected
