oracle blob 文本搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2963793/
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
oracle blob text search
提问by Skay
Is it possible to search through blob text using sql statement? I can do select * from $table where f1 like '%foo%' if the f1 is varchar, how about f1 is a blob? Any counter part for this?
是否可以使用 sql 语句搜索 blob 文本？我可以选择 * from $table where f1 like '%foo%' 如果 f1 是 varchar，那么 f1 是一个 blob 怎么样？这有什么对应的部分吗？
回答by Olafur Tryggvason
This is quite possible and easy to do.
这是完全可能且容易做到的。
Simply use dbms_lob.instr in conjunction with utl_raw.cast_to_raw
只需将 dbms_lob.instr 与 utl_raw.cast_to_raw 结合使用
So in your case, if t1 is a BLOB the select would look like:
因此，在您的情况下，如果 t1 是 BLOB，则选择将如下所示：
select *
from table1
where dbms_lob.instr (t1, -- the blob
utl_raw.cast_to_raw ('foo'), -- the search string cast to raw
1, -- where to start. i.e. offset
1 -- Which occurrance i.e. 1=first
) > 0 -- location of occurrence. Here I don't care. Just find any
;
回答by Gary Myers
If it is a Word or PDF document, look into Oracle Text.
如果是 Word 或 PDF 文档，请查看Oracle Text。
回答by Tony Andrews
If you are storing plain text it should be a CLOB, not a BLOB, and then you can still query using LIKE. A BLOB contains binary data that Oracle doesn't know the structure of, so it cannot search it in this way.
如果您存储纯文本，它应该是 CLOB，而不是 BLOB，然后您仍然可以使用 LIKE 进行查询。BLOB 包含 Oracle 不知道其结构的二进制数据，因此它无法以这种方式搜索它。
This works for CLOBs of anylength (at least on Oracle 12C):
这适用于任何长度的CLOB （至少在 Oracle 12C 上）：
SQL> create table t1 (c clob);
Table created.
SQL> declare
2 x clob;
3 begin
4 for i in 1..100 loop
5 x := x || rpad('x', 32767, 'x');
6 end loop;
7 x := x || 'z';
8 for i in 1..100 loop
9 x := x || rpad('x', 32767, 'x');
10 end loop;
11 insert into t1 values (x);
12 end;
13 /
PL/SQL procedure successfully completed.
SQL> select dbms_Lob.getlength(c) from t1 where c like '%z%';
DBMS_LOB.GETLENGTH(C)
---------------------
6553401
Note that there is only one 'z' in that 6,554,401 byte CLOB - right in the middle of it:
请注意，在 6,554,401 字节的 CLOB 中只有一个“z”——就在它的中间：
SQL> select instr(c, 'z') from t1;
INSTR(C,'Z')
------------
3276701
回答by NoOoNY
the below code is to display the details from blob as text using UTL_RAW.CAST_TO_VARCHAR2 functionthen we use substr functionto cut the text from the start of expected data till end. however, you can use instr function, LENGTH function, if you know the location of the data you are looking for
下面的代码是使用UTL_RAW.CAST_TO_VARCHAR2 函数将blob 中的详细信息显示为文本，然后我们使用substr 函数将文本从预期数据的开头剪切到结尾。但是， 如果您知道要查找的数据的位置，则 可以使用 instr 函数、LENGTH 函数
select NVL(SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body),
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'),
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '</ns:xml_element>') - (
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'))),
utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(blob_body))
) blob_body
from dual
where SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body),
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'),
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '</ns:xml_element>') - (
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'))) like '%foo%';
回答by Z Vijaya Kumar
Select * From TABLE_NAME and dbms_lob.instr("BLOB_VARIABLE_NAME", utl_raw.cast_to_raw('search_text'), 1, 1) > 0
Select * From TABLE_NAME and dbms_lob.instr("BLOB_VARIABLE_NAME", utl_raw.cast_to_raw('search_text'), 1, 1) > 0
