oracle 如何从 SQL 中的文本 CLOB 字段中提取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20955824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract values from a text CLOB field in SQL
提问by Steve
I have an SQL table column (BINARYTEXT) populated with a CLOB. Within this CLOB is a number of attributes e.g.
我有一个填充了 CLOB 的 SQL 表列 (BINARYTEXT)。在这个 CLOB 中有许多属性，例如
CE.EffDate="20140106";
CE.CCY="EUR";
CE.TransactionType="STANDARDEUROPEAN";
CE.CAL="LON";
I need to extract only the value of the CE.TransactionType attribute contained between the double quotes so 'STANDARDEUROPEAN'. Note that the CLOB does not contain XML and only contains attributes as above with no start or end tags.
我只需要提取双引号之间包含的 CE.TransactionType 属性的值，所以 'STANDARDEUROPEAN'。请注意，CLOB 不包含 XML，仅包含上述属性，没有开始或结束标记。
I have worked out how to do this using the REGEXP_SUBSTR function when I specify the string in the command:
当我在命令中指定字符串时，我已经弄清楚如何使用 REGEXP_SUBSTR 函数执行此操作：
select REGEXP_SUBSTR('CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+') transtype
from DUAL
which returns: ="STANDARDEUROPEAN
返回：="STANDARDEUROPEAN
I am unable to manipulate this into using the CLOB as the string. This does not work:
我无法将其操作为使用 CLOB 作为字符串。这不起作用：
select REGEXP_SUBSTR(BINARYTEXT,'CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+') transtype
from DUAL
Thanks in advance,
提前致谢，
Steve
史蒂夫
回答by StevieG
You can use the DBMS_LOB package to extract the string.
您可以使用 DBMS_LOB 包来提取字符串。
SELECT REGEXP_SUBSTR(DBMS_LOB.substr(BINARYTEXT,3000) ,'CE.TransactionType="STANDARDEUROPEAN"', '="[^"]+') transtype
FROM YOURTABLENAME
The only thing you might have problems with is the buffer size, which is restricted to 32767 bytes (its set to 3000 in my example). If your data is bigger than this, then you'd need to query it in chunks. See the documentation here
您唯一可能遇到的问题是缓冲区大小，它限制为 32767 字节（在我的示例中设置为 3000）。如果您的数据比这大，那么您需要分块查询。请参阅此处的文档
If you need to change the TransactionType you're searching for, you should probably create a function which takes that part of the string as a parameter and build the sql dynamically.
如果您需要更改您正在搜索的 TransactionType，您可能应该创建一个函数，该函数将字符串的那部分作为参数并动态构建 sql。
回答by Sai
select BINARYTEXT from your_table
where regexp_like(dat,'^(\CE.TransactionType=)?"[^"]+"$');
EDIT2:
select BINARYTEXT from your_table
where regexp_like(dat,'^(\CE.TransactionType=)?"[^"]+"?;$');
