blocks|key|3382014|text|您可以使用regexp_substr()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3382015|select+regexp_substr(val,+'[%5E,]%2B',+1,+1)+as+val1,+
+++++++regexp_substr(val,+'[%5E,]%2B',+1,+2)+as+val2,+
+++++++regexp_substr(val,+'[%5E,]%2B',+1,+3)+as+val3,+
+++++++.+.+.|code-block|syntax|javascript|3382016|我建议您在Excel+(或其他电子表格)中生成255个数字的列，并使用电子表格生成SQL代码。|3382017|entityMap^0|5|F|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

You can use <code>regexp_substr()</code>:

<pre><code>select regexp_substr(val, '[^,]+', 1, 1) as val1, 
 regexp_substr(val, '[^,]+', 1, 2) as val2, 
 regexp_substr(val, '[^,]+', 1, 3) as val3, 
 . . .
</code></pre>

I would suggest that you generate a column of 255 numbers in Excel (or another spreadsheet), and use the spreadsheet to generate the SQL code.

blocks|key|3112609|text|当心！如果列表中有一个空元素，并且您想要该项或它后面的一个，'[%5E,]%2B'格式的'[%5E,]%2B'表达式将不会返回预期值。考虑这个示例，其中第4个元素为NULL，我希望第5个元素，因此期望返回'5‘：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3112610|SQL>+select+regexp_substr('1,2,3,,5,6',+'[%5E,]%2B',+1,+5)+from+dual;

R
-
6|code-block|syntax|javascript|3112611|惊喜吧！它返回第5个非空元素，而不是实际的第5个元素！返回的数据不正确，甚至可能抓不到它。试一试：|3112612|SQL>+select+regexp_substr('1,2,3,,5,6',+'(.*?)(,%7C$)',+1,+5,+NULL,+1)+from+dual;

R
-
5|3112613|因此，上面更正的REGEXP_SUBSTR表示要查找0或更多逗号分隔字符的第5次出现，后面跟着逗号或行尾(允许下一个分隔符，无论是逗号还是行尾)，并在被发现时返回第一个子组(不包括逗号或行尾的数据)。|3112614|搜索匹配模式'(.*?)(,%7C$)'解释道：|3112615|(+++++++++++++=+Start+a+group
.+++++++++++++=+match+any+character
*+++++++++++++=+0+or+more+matches+of+the+preceding+character
?+++++++++++++=+Match+0+or+1+occurrences+of+the+preceding+pattern
)+++++++++++++=+End+the+1st+group
(+++++++++++++=+Start+a+new+group+(also+used+for+logical+OR)
,+++++++++++++=+comma
%7C+++++++++++++=+OR
$+++++++++++++=+End+of+the+line
)+++++++++++++=+End+the+2nd+group|3112616|编辑:更多的信息添加和简化了正则表达式。|3112617|有关更多信息和将其封装到函数中以便于重用的建议，请参阅这篇文章：REGEX+to+select+nth+value+from+a+list,+allowing+for+nulls，这是我发现'[%5E,]%2B'格式有问题的帖子。不幸的是，这是您最常看到的regex格式，用于回答有关如何解析列表的问题。想到'[%5E,]%2B'返回的所有不正确的数据，我会不寒而栗！|3112618|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/25648653/regex-to-select-nth-value-from-a-list-allowing-for-nulls/25652018#25652018^0|U|7|14|7|0|0|0|0|0|6|C|0|0|0|2N|7|46|7|W|1L|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1C|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1F|8|@$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|S|3|T|5|H|7|1I|8|@]|D|@]|E|$I|J]]|$1|U|3|V|5|6|7|1J|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1K|8|@$9|1L|A|1M|B|C]|$9|1N|A|1O|B|C]]|D|@$9|1P|A|1Q|1|1R]]|E|$]]|$1|Y|3|-4|5|6|7|1S|8|@]|D|@]|E|$]]]|Z|$10|$5|11|12|13|E|$14|15]]]]

Beware! The regexp_substr expression of the format <code>'[^,]+'</code> will not return the expected value if there is a null element in the list and you want that item or one after it. Consider this example where the 4th element is NULL and I want the 5th element and thus expect the '5' to be returned:

<pre><code>SQL&gt; select regexp_substr('1,2,3,,5,6', '[^,]+', 1, 5) from dual;

R
-
6
</code></pre>

Surprise! It returns the 5th NON-NULL element, not the actual 5th element! Incorrect data returned and you may not even catch it. Try this instead:

<pre><code>SQL&gt; select regexp_substr('1,2,3,,5,6', '(.*?)(,|$)', 1, 5, NULL, 1) from dual;

R
-
5
</code></pre>

So, the above corrected REGEXP_SUBSTR says to look for the 5th occurrence of 0 or more comma-delimited characters followed by a comma or the end of the line (allows for the next separator, be it a comma or the end of the line) and when found return the 1st subgroup (the data NOT including the comma or end of the line).

The search match pattern <code>'(.*?)(,|$)'</code> explained:

<pre><code>( = Start a group
. = match any character
* = 0 or more matches of the preceding character
? = Match 0 or 1 occurrences of the preceding pattern
) = End the 1st group
( = Start a new group (also used for logical OR)
, = comma
| = OR
$ = End of the line
) = End the 2nd group
</code></pre>

EDIT: More info added and simplified the regex.

See this post for more info and a suggestion to encapsulate this in a function for easy reuse: <a href="https://stackoverflow.com/questions/25648653/regex-to-select-nth-value-from-a-list-allowing-for-nulls/25652018#25652018">REGEX to select nth value from a list, allowing for nulls</a>
It's the post where I discovered the format <code>'[^,]+'</code> has the problem. Unfortunately it's the regex format you will most commonly see as the answer for questions regarding how to parse a list. I shudder to think of all the incorrect data being returned by <code>'[^,]+'</code>!

blocks|key|3382063|text|如果您只有一行，并且有时间创建|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3382064|创建您自己的内置cto_table+function+to+split+a+string+on+any+separator，然后可以使用PIVOT+%2B+LISTAGG进行如下操作：|unordered-list-item|offset|length|style|CODE|3382065|select+*+from+(
++select+rownum+r+,+collection.*++
++++from+TABLE(cto_table(',','1.25,+3.87,+2,+19,,+1,+9,+'))+collection
)
PIVOT+(+
++LISTAGG(column_value)+within+group+(order+by+1)+as+val+
++for+r+in+(1,+2,+3,+4,+5,+6,+7,+8,+9,+10)
)|code-block|syntax|javascript|3382066|FYI:下面是如何创建cto_table函数：|3382067|CREATE+OR+REPLACE+TYPE+t_my_list+AS+TABLE+OF+VARCHAR2(100);
CREATE+OR+REPLACE
FUNCTION+cto_table(p_sep+in+Varchar2,+p_list+IN+VARCHAR2)
++RETURN+t_my_list
AS
++l_string+VARCHAR2(32767)+:=+p_list+%7C%7C+p_sep;
++l_sep_index+PLS_INTEGER;
++l_index+PLS_INTEGER+:=+1;
++l_tab+t_my_list+++++:=+t_my_list();
BEGIN
++LOOP
++++l_sep_index+:=+INSTR(l_string,+p_sep,+l_index);
++++EXIT
++WHEN+l_sep_index+=+0;
++++l_tab.EXTEND;
++++l_tab(l_tab.COUNT)+:=+TRIM(SUBSTR(l_string,l_index,l_sep_index+-+l_index));
++++l_index++++++++++++:=+l_sep_index+%2B+1;
++END+LOOP;
++RETURN+l_tab;
END+cto_table;
/|3382068|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/41761187/6019417^0|0|8|9|1W|F|I|17|0|0|0|B|9|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@$E|11|F|12|G|H]|$E|13|F|14|G|H]]|9|@$E|15|F|16|1|17]]|A|$]]|$1|I|3|J|5|K|7|18|8|@]|9|@]|A|$L|M]]|$1|N|3|O|5|6|7|19|8|@$E|1A|F|1B|G|H]]|9|@]|A|$]]|$1|P|3|Q|5|K|7|1C|8|@]|9|@]|A|$L|M]]|$1|R|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]]]

If you only have one row, and time to create your 

<ul>
<li>create your own built-in <code>cto_table</code> <a href="https://stackoverflow.com/a/41761187/6019417">function to split a string on any separator</a>, then you can use <code>PIVOT + LISTAGG</code> to do it like follows:</li>
</ul>

<hr>

<pre><code>select * from (
 select rownum r , collection.* 
 from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection
)
PIVOT ( 
 LISTAGG(column_value) within group (order by 1) as val 
 for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)
</code></pre>

<hr>

FYI: here is how to create the <code>cto_table</code> function:

<pre><code>CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
 RETURN t_my_list
AS
 l_string VARCHAR2(32767) := p_list || p_sep;
 l_sep_index PLS_INTEGER;
 l_index PLS_INTEGER := 1;
 l_tab t_my_list := t_my_list();
BEGIN
 LOOP
 l_sep_index := INSTR(l_string, p_sep, l_index);
 EXIT
 WHEN l_sep_index = 0;
 l_tab.EXTEND;
 l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
 l_index := l_sep_index + 1;
 END LOOP;
 RETURN l_tab;
END cto_table;
/
</code></pre>

blocks|key|3112645|text|可以使用分层查询。旋转可以通过案例和分组完成。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3112646|with+value_t+as
(+
++select+row_t,row_number()+OVER+(partition+by+row_t+order+by+rownum+)rn,
++regexp_substr(val,+'[%5E,]%2B',+1,+LEVEL)+val+from+Table1
CONNECT+BY+LEVEL+<=+regexp_count(val,+'[%5E,]%2B')+
AND+prior+row_t+=+row_t+
AND+prior+sys_guid()+is+not+null
++)+select+row_t,+max(+case+when+rn+=+1+THEN+val+end+)+val_1,
++max(+case+when+rn+=+2+THEN+val+end+)+val_2,
++max(+case+when+rn+=+3+THEN+val+end+)+val_3
++from+value_t
++group+by+row_t;|code-block|syntax|javascript|3112647|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

hierarchical query could be used. pivoting can be done with case and group by.

<pre><code>with value_t as
( 
 select row_t,row_number() OVER (partition by row_t order by rownum )rn,
 regexp_substr(val, '[^,]+', 1, LEVEL) val from Table1
CONNECT BY LEVEL &lt;= regexp_count(val, '[^,]+') 
AND prior row_t = row_t 
AND prior sys_guid() is not null
 ) select row_t, max( case when rn = 1 THEN val end ) val_1,
 max( case when rn = 2 THEN val end ) val_2,
 max( case when rn = 3 THEN val end ) val_3
 from value_t
 group by row_t;
</code></pre>

I have values being returned with 255 comma separated values. Is there an easy way to split those into columns without having 255 substr?

<pre><code>ROW | VAL
----------- 
1 | 1.25, 3.87, 2, ... 
2 | 5, 4, 3.3, ....
</code></pre>

to

<pre><code>ROW | VAL | VAL | VAL ...
--------------------- 
1 |1.25 |3.87 | 2 ... 
2 | 5 | 4 | 3.3 ...
</code></pre>

Split comma separated values to columns in Oracle

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

返回值时使用255个逗号分隔的值。是否有一种不需要255个substr就可以将这些列分割成列的简单方法？ROW  | VAL----------- 1    | 1.25, 3.87, 2, ... 2    | 5, 4, 3.3, ....至ROW | VAL | VAL | VAL ...------------...

问将逗号分隔为Oracle中的列
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将逗号分隔为Oracle中的列EN