apex_string.split 新行作为分隔符

发布于 2025-01-10 11:16:27 字数 279 浏览 0 评论 0原文

我正在使用此查询来拆分 SQL 查询中文本区域字段的字符串：

select * from "MyTable" a 
where NAME in (select * from 
TABLE(apex_string.split(:P23_TEXTAREA, ',')))

它可以工作，但我想使用新行分隔符而不是逗号来拆分字符串。我已经尝试过“\n”、“\r\n”、“
”但没有成功。如果我删除分隔符并使用默认分隔符，则字符串仅用新行分割一次。如何用多个新行分隔的条目分割字符串？

原文

I am using this query to split a string of a text area field inside my SQL query:

select * from "MyTable" a 
where NAME in (select * from 
TABLE(apex_string.split(:P23_TEXTAREA, ',')))

It works but I would like to split the string with a new line delimiter instead of a comma. I tried already "\n", "\r\n", "
" but without success. If I remove the delimiter and use the default, the string gets split only once with a new line. How can I split my string with multiple new line separated entries?

分享到QQ

分享到微博

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

发布评论

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

木有鱼丸 2025-01-17 11:16:27

您可以尝试使用 CHR 函数：

SELECT *
FROM   TABLE(apex_string.split(:P23_TEXTAREA, CHR(10)))

或包含换行符的字符串文字：

SELECT *
FROM   TABLE(apex_string.split(:P23_TEXTAREA, '
'))

您的 :P23_TEXTAREA 值为：'TRI_1000'||CHR(13)||CHR (10)||'TRI_10'，如果您将其拆分为 CHR(10) (LF)，将为您提供值'TRI_1000'||CHR(13) 和 'TRI_10' 以及第二个可能会匹配，但第一个由于尾随回车 (CR) 字符而不会匹配。

看来您需要修剪结果或按 CR/LF （或可选的 CR）拆分，而不仅仅是 LF:

SELECT *
FROM   "MyTable"
WHERE  name IN (SELECT * 
                FROM   TABLE(apex_string.split(:P23_TEXTAREA, '\r?\n'))
               )

或：

SELECT *
FROM   "MyTable"
WHERE  name IN (SELECT RTRIM(COLUMN_VALUE, CHR(13)) 
                FROM   TABLE(apex_string.split(:P23_TEXTAREA, CHR(10)))
               )

You can try the CHR function:

SELECT *
FROM   TABLE(apex_string.split(:P23_TEXTAREA, CHR(10)))

or a string literal containing a newline:

SELECT *
FROM   TABLE(apex_string.split(:P23_TEXTAREA, '
'))

Your :P23_TEXTAREA value is: 'TRI_1000'||CHR(13)||CHR(10)||'TRI_10' which, if you split it on CHR(10) (LF) will give you the values 'TRI_1000'||CHR(13) and 'TRI_10' and the second will probably match but the first will not due to the trailing carriage return (CR) character.

It appears you need to either trim the result or split on CR/LF (or an optional CR) rather than just LF:

SELECT *
FROM   "MyTable"
WHERE  name IN (SELECT * 
                FROM   TABLE(apex_string.split(:P23_TEXTAREA, '\r?\n'))
               )

or:

SELECT *
FROM   "MyTable"
WHERE  name IN (SELECT RTRIM(COLUMN_VALUE, CHR(13)) 
                FROM   TABLE(apex_string.split(:P23_TEXTAREA, CHR(10)))
               )

回复收藏 0 原文

撧情箌佬 2025-01-17 11:16:27

apex_string.split 的默认分隔符是 LF（换行），因此一种方法是在将 CR+LF 组合传递给 split 之前将其替换为单个 LF：

select * from "MyTable" a 
where NAME in (select * from 
    TABLE(apex_string.split(
        replace(:P23_TEXTAREA, chr(13)||chr(10), chr(10))
    ))
)

The default delimiter for apex_string.split is LF (line feed) so one way to do this is to replace the CR+LF combos with a single LF before passing it to split:

select * from "MyTable" a 
where NAME in (select * from 
    TABLE(apex_string.split(
        replace(:P23_TEXTAREA, chr(13)||chr(10), chr(10))
    ))
)

回复收藏 0 原文

~没有更多了~