将字符串拆分成行Oracle SQL [英] Split String into rows Oracle SQL
问题描述
在搜索了论坛之后,我提出了以下内容,但是它不起作用:/
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|
.
.
.
请不要让大约有20,000行,我的另一选择是编写一个存储过程来做到这一点...基本上,我需要将字符串分开,以便每个这样的行都有
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
ID | Strings
123| abc
123| fgh
123| dwd
243| dfs
以此类推...
这就是我所拥有的.
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;
感谢您的帮助
推荐答案
这应该可以解决问题:
SELECT DISTINCT ID, regexp_substr("Strings", '[^ ]+', 1, LEVEL)
FROM T
CONNECT BY regexp_substr("Strings", '[^ ]+', 1, LEVEL) IS NOT NULL
ORDER BY ID;
请注意我也如何在connect by子句中使用regexp_substr
.这是为了处理多个空格的情况.
Notice how I used regexp_substr
in the connect by clause too. This is to deal with the case of multiple spaces.
如果每行项目数的上限是可预测的,则可能需要将上面的递归查询的性能与简单的CROSS JOIN
进行比较:
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
:
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;
请参见 http://sqlfiddle.com/#!4/444e3/1 进行现场演示
这篇关于将字符串拆分成行Oracle SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!