Oracle 将文本拆分为多行

发布于 2024-12-13 05:22:57 字数 284 浏览 0 评论 0原文

在 varchar2 列中，我有如下文本值：

aaaaaa. fgdfg.
bbbbbbbbbbbbbb ccccccccc
dddddd ddd dddddddddddd,
asdasdasdll
sssss

如果我从表中选择列，其中 id=...，通常我会在一行中得到整个文本。但我想获得多行结果，上面的例子是 5 行。我必须只使用一个 select 语句，分隔符将是换行符或回车符（oracle 中的 chr(10)、chr(13)）

谢谢！

原文

Inside a varchar2 column I have text values like :

aaaaaa. fgdfg.
bbbbbbbbbbbbbb ccccccccc
dddddd ddd dddddddddddd,
asdasdasdll
sssss

if i do select column from table where id=... i get the whole text in a single row, normally.
But i would like to get the result in multiple rows, 5 for the example above.
I have to use just one select statement, and the delimiters will be new line or carriage return (chr(10), chr(13) in oracle)

Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

巷雨优美回忆 2024-12-20 05:22:57

像这样，也许（但这完全取决于您使用的 Oracle 版本）：

WITH yourtable AS    (SELECT REPLACE('aaaaaa. fgdfg.' ||chr(10)||           
                        'bbbbbbbbbbbbbb ccccccccc  ' ||chr(13)|| 
                        'dddddd ddd dddddddddddd,' ||chr(10)||
                        'asdasdasdll  ' ||chr(13)||
                        'sssss '||chr(10),chr(13),chr(10)) AS astr FROM DUAL)
SELECT REGEXP_SUBSTR ( astr, '[^' ||chr(10)||']+', 1, LEVEL) data   FROM yourtable   
CONNECT BY LEVEL <= LENGTH(astr) - LENGTH(REPLACE(astr, chr(10))) + 1

请参阅：逗号Oracle 中的分隔值

Like this, maybe (but it all depends on the version of oracle you are using):

WITH yourtable AS    (SELECT REPLACE('aaaaaa. fgdfg.' ||chr(10)||           
                        'bbbbbbbbbbbbbb ccccccccc  ' ||chr(13)|| 
                        'dddddd ddd dddddddddddd,' ||chr(10)||
                        'asdasdasdll  ' ||chr(13)||
                        'sssss '||chr(10),chr(13),chr(10)) AS astr FROM DUAL)
SELECT REGEXP_SUBSTR ( astr, '[^' ||chr(10)||']+', 1, LEVEL) data   FROM yourtable   
CONNECT BY LEVEL <= LENGTH(astr) - LENGTH(REPLACE(astr, chr(10))) + 1

see: Comma Separated values in Oracle

回复收藏 0 原文

忘东忘西忘不掉你 2024-12-20 05:22:57

如果您的数据包含空行，Kevin Burton 的答案包含一个错误。
以下改编基于发明的解决方案这里，有效。查看该帖子以获取有关问题和解决方案的说明。

WITH yourtable AS    (SELECT REPLACE('aaaaaa. fgdfg.' ||chr(10)||           
                    'bbbbbbbbbbbbbb ccccccccc  ' ||chr(13)|| 
                    chr(13)||
                    'dddddd ddd dddddddddddd,' ||chr(10)||
                    'asdasdasdll  ' ||chr(13)||
                    'sssss '||chr(10),chr(13),chr(10)) AS astr FROM DUAL)
SELECT REGEXP_SUBSTR ( astr, '([^' ||chr(10)||']*)('||chr(10)||'|$)', 1,  LEVEL, null, 1) data   FROM yourtable   
CONNECT BY LEVEL <= LENGTH(astr) - LENGTH(REPLACE(astr, chr(10))) + 1;

The answer by Kevin Burton contains a bug if your data contains empty lines.
The adaptation below, based on the solution invented here, works. Check that post for an explanation on the issue and the solution.

WITH yourtable AS    (SELECT REPLACE('aaaaaa. fgdfg.' ||chr(10)||           
                    'bbbbbbbbbbbbbb ccccccccc  ' ||chr(13)|| 
                    chr(13)||
                    'dddddd ddd dddddddddddd,' ||chr(10)||
                    'asdasdasdll  ' ||chr(13)||
                    'sssss '||chr(10),chr(13),chr(10)) AS astr FROM DUAL)
SELECT REGEXP_SUBSTR ( astr, '([^' ||chr(10)||']*)('||chr(10)||'|$)', 1,  LEVEL, null, 1) data   FROM yourtable   
CONNECT BY LEVEL <= LENGTH(astr) - LENGTH(REPLACE(astr, chr(10))) + 1;

回复收藏 0 原文

~没有更多了~