当前位置：文江博客话题详情

Oracle Blob 文本搜索

发布于 2024-09-03 14:53:16 字数 122 浏览 6 评论 0原文

是否可以使用 sql 语句搜索 blob 文本？我可以 select * from $table where f1 like '%foo%' 如果 f1 是 varchar，那么 f1 是一个 blob 怎么样？有什么对应的吗？

原文

分享到QQ

分享到微博

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

发布评论

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

巡山小妖精 2024-09-10 14:53:16

这是完全可能且容易做到的。

只需将 dbms_lob.instr 与 utl_raw.cast_to_raw 结合使用

因此在您的情况下，如果 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
;

This is quite possible and easy to do.

Simply use dbms_lob.instr in conjunction with utl_raw.cast_to_raw

So in your case, if t1 is a BLOB the select would look like:

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
;

回复收藏 0 原文

冰雪梦之恋 2024-09-10 14:53:16

如果是 Word 或 PDF 文档，请查看 Oracle Text 。

回复收藏 0 原文

太阳哥哥 2024-09-10 14:53:16

如果您存储纯文本，它应该是 CLOB，而不是 BLOB，然后您仍然可以使用 LIKE 进行查询。 BLOB 包含 Oracle 不知道其结构的二进制数据，因此无法以这种方式搜索它。

这适用于任意长度的 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

请注意，6,554,401 字节的 CLOB 中只有一个“z” - 位于其中间：

SQL> select instr(c, 'z') from t1;

INSTR(C,'Z')
------------
     3276701

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.

This works for CLOBs of any length (at least on 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:

SQL> select instr(c, 'z') from t1;

INSTR(C,'Z')
------------
     3276701

回复收藏 0 原文

我ぃ本無心為│何有愛 2024-09-10 14:53:16

下面的代码是使用 UTL_RAW.CAST_TO_VARCHAR2 函数然后我们使用 substr 函数将文本从预期数据的开头剪切到结尾。但是，您可以使用指令函数， 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%';

the below code is to display the details from blob as text using UTL_RAW.CAST_TO_VARCHAR2 function then we use substr function to 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

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%';

回复收藏 0 原文