用新行分割字符串，并在PL sql Oracle中添加数组

问题描述

我有一个如下所示的字符串:

I have a string like below:

你在哪里

你叫什么名字

我想用新行分割上面的字符串，并添加到数组，如下所示:

I want to split the above string with new line and add to array as below:

在PL/SQL中有可能吗?

Is this possible in PL/SQL ?

推荐答案

为什么已经和汤姆打招呼了，又问汤姆的名字?

Why do you ask Tom's name again when you have already said hello to him?

无论如何..是的.您可以对由CHR(10)分隔的字符串(Oracle中的换行符)使用标准的字符串拆分操作.然后使用CAST和COLLECT函数将其转换为数组.在这里，我使用了Oracle的内置集合sys.OdciVarchar2List.在您的PL/SQL块中，您可以将其批量收集为可以容纳字符串元素的任何适当的收集类型.

Anyways.. Yes. You could use the standard string split operation on string delimited by CHR(10) - newline character in Oracle. Then make use of CAST and COLLECT functions to convert it to an array. Here I have used Oracle's built-in collection sys.OdciVarchar2List. In your PL/SQL block you may BULK COLLECT it into any proper collection type that can hold string elements.

SQL提琴

WITH t (s)
AS (
    SELECT 'Hello Tom
Where are you
What''s your name'
    FROM DUAL
    )
SELECT CAST ( COLLECT ( REGEXP_SUBSTR(s, '[^' || CHR(10)|| ']+', 1, LEVEL) )
                       AS sys.OdciVarchar2List )  as collection
FROM t CONNECT BY LEVEL <= REGEXP_COUNT(s, '[^' || CHR(10)|| ']+')

结果 :

Results:

|                               COLLECTION |
|------------------------------------------|
| Hello Tom,Where are you,What's your name |

这篇关于用新行分割字符串，并在PL sql Oracle中添加数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！