检查 clob 是否包含字符串 oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44025050/
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
check if clob contains string oracle
提问by Master Yi
currently i have query with this code to_char(CLOB_COLUM) like %sbut the following wont work for very big clob. Is there another solution to check if this column contains some string. Using oracle 11.2.0.4.0
目前我有这个代码的查询，to_char(CLOB_COLUM) like %s但以下不适用于非常大的clob。是否有另一种解决方案来检查此列是否包含某些字符串。使用 Oracle 11.2.0.4.0
回答by MT0
You can use DBMS_LOB.INSTR( clob_value, pattern [, offset [, occurrence]] ):
您可以使用DBMS_LOB.INSTR( clob_value, pattern [, offset [, occurrence]] )：
SELECT *
FROM your_table
WHERE DBMS_LOB.INSTR( clob_column, 'string to match' ) > 0;
or
或者
SELECT *
FROM your_table
WHERE clob_column LIKE '%string to match%';
回答by alan9uo
Base on MT0's answer. I test which way is efficient.
基于 MT0 的回答。我测试哪种方式有效。
The CLOB Columnlength is 155018and search for 32 lengthstring.
的CLOB列长度是155018和搜索32长度的字符串。
Here is my test.
这是我的测试。
| INSTR | LIKE |
|:-------|------:|
| 0.857 |0.539 |
| 0.127 |0.179 |
| 1.635 |0.534 |
| 0.511 |0.818 |
| 0.429 |1.038 |
| 1.586 |0.772 |
| 0.461 |0.172 |
| 0.126 |1.379 |
| 1.068 |1.088 |
| 1.637 |1.169 |
| 0.5 |0.443 |
| 0.674 |0.432 |
| 1.201 |0.135 |
| 0.419 |2.057 |
| 0.731 |0.462 |
| 0.787 |1.956 |
The average time of INSTRis 0.797.
INSTR的平均时间 为 0.797。
The average time of LIKEis 0.823.
LIKE的平均时间 为 0.823。
回答by Srdjan
If you want to see the column's value and Oracle returns ORA-22835(buffer too small) for WHERE clob_column LIKE '%string to match%', then you should to apply some workaround.
如果您想查看列的值并且 Oracle 为 返回ORA-22835（缓冲区太小）WHERE clob_column LIKE '%string to match%'，那么您应该应用一些解决方法。
The combination of DBMS_LOB.instrand DBMS_LOB.substrcould be a solution. See e.g. this Stackoverflow tip. So, in your case:
的组合DBMS_LOB.instr以及DBMS_LOB.substr可能的解决方案。参见例如这个 Stackoverflow 技巧。所以，在你的情况下：
SELECT DBMS_LOB.substr(your_clob_column, DBMS_LOB.instr(your_clob_column,'string to match'), 1) AS Text
FROM your_table
WHERE DBMS_LOB.instr(your_clob_column, 'string to match') > 0
