用于根据字符将单行数据拆分为多行的SQL(SQL for splitting a single row data into multiple rows based on character)

到目前为止，我已经编写了一个查询，将第一行拆分为多行，但后面N行的结果将是N行返回空值。

这是场景。

select address from sample;

哪个会在4行后返回，

Stack Overflow# is a# question and# answer site Stack Overflow# is a# question and# answer site Stack Overflow# is a# question and# answer site Stack Overflow# is a# question and# answer site

尝试使用以下查询将每行拆分为多行时，

with test as (select address as str from sample) select regexp_substr (str, '[^#]+', 1, rownum) split from test connect by level <= length (regexp_substr (str, '[^#]+', 1, rownum)) + 1 ;

将返回以下值。

Stack Overflow is a question and answer site (null) (null) (null)

为什么我无法获得所有行的结果？

So far I have written a query that splits the first row into multiple rows but the result for the following N rows will be N rows returning null values.

Here is the scenario.

select address from sample;

Which would return following 4 rows,

Stack Overflow# is a# question and# answer site Stack Overflow# is a# question and# answer site Stack Overflow# is a# question and# answer site Stack Overflow# is a# question and# answer site

When tried to split each row into multiple rows using the following query,

with test as (select address as str from sample) select regexp_substr (str, '[^#]+', 1, rownum) split from test connect by level <= length (regexp_substr (str, '[^#]+', 1, rownum)) + 1 ;

The following values will be returned.

Stack Overflow is a question and answer site (null) (null) (null)

Why cant I get the results for all rows?

最满意答案

为什么我无法获得所有行的结果？

您的查询有两件事不正确。

因为使用ROWNUM是不正确的。您在同一查询中使用ROWNUM作为条件，但是，ROWNUM尚未递增到下一个值 。所以，它的价值只有一个。所以，你只得到一排。

您需要对所有行进行拆分，而不仅仅是第一行。您需要循环遍历所有行。但是，与此同时，你应该避免循环循环并摆脱重复。

有多种方法可以为多行执行字符串拆分。我在我的文章中已经证明了这里http://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-using-oracle-sql/

例如，你可以这样做：

SQL> WITH t AS( 2 SELECT 'Stack Overflow# is a# question and# answer site' text FROM dual UNION ALL 3 SELECT 'Stack Overflow# IS a# question and# answer site' text FROM dual UNION ALL 4 SELECT 'Stack Overflow# is a# question and# answer site' text FROM dual UNION ALL 5 SELECT 'Stack Overflow# IS a# question and# answer site' text FROM dual 6 ) 7 SELECT trim(regexp_substr(t.text, '[^#]+', 1, lines.column_value)) text 8 FROM t, 9 TABLE (CAST (MULTISET 10 (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, '#')+1) 11 AS sys.odciNumberList 12 ) 13 ) lines 14 / TEXT ----------------------------------------------- Stack Overflow is a question and answer site Stack Overflow IS a question and answer site Stack Overflow is a question and answer site Stack Overflow IS a question and answer site 16 rows selected. SQL>

所以，你现在得到16行 。完美的工作！

Why cant I get the results for all rows?

There are two things incorrect with your query.

Because the use of ROWNUM is incorrect. You are using the ROWNUM in the same query as a condition, however, the ROWNUM is not yet incremented to next value. so, it's value is just one. So, you just get only 1 row.

You need to do the split for all the rows and not just the first row. You need to loop through all the rows. But, at the same time you should avoid a cyclic-loop and get rid of duplicates.

There are many ways of doing the string split for multiple rows. I have demonstrated in my article here http://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-using-oracle-sql/

For example, you could do it like this:

SQL> WITH t AS( 2 SELECT 'Stack Overflow# is a# question and# answer site' text FROM dual UNION ALL 3 SELECT 'Stack Overflow# IS a# question and# answer site' text FROM dual UNION ALL 4 SELECT 'Stack Overflow# is a# question and# answer site' text FROM dual UNION ALL 5 SELECT 'Stack Overflow# IS a# question and# answer site' text FROM dual 6 ) 7 SELECT trim(regexp_substr(t.text, '[^#]+', 1, lines.column_value)) text 8 FROM t, 9 TABLE (CAST (MULTISET 10 (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, '#')+1) 11 AS sys.odciNumberList 12 ) 13 ) lines 14 / TEXT ----------------------------------------------- Stack Overflow is a question and answer site Stack Overflow IS a question and answer site Stack Overflow is a question and answer site Stack Overflow IS a question and answer site 16 rows selected. SQL>

So, you now get 16 rows. Works perfectly!

更多推荐