在Oracle Clob列中搜索特定的字符串 [英] Search for a particular string in Oracle clob column

问题描述

如何从Clob列中获取特定字符串?

How to get a particular string from a clob column?

我有以下数据，这些数据存储在名为product_details

I have data as below which is stored in clob column called product_details

CALCULATION=[N]NEW.PRODUCT_NO=[T9856] 
OLD.PRODUCT_NO=[T9852].... -- with other text

我想从列product_details

我尝试过

select * from my_table
where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1

以上内容从我的表格中获取了全文.

The above fetches full text from my table.

任何帮助都是非常重要的.

Any help is highly appreciable.

致谢

推荐答案

使用 dbms_lob.instr 和 dbms_lob.substr 常规的InStr和SubstStr函数.
看一个简单的例子:

Use dbms_lob.instr and dbms_lob.substr, just like regular InStr and SubstStr functions.
Look at simple example:

SQL> create table t_clob(
  2    id number,
  3    cl clob
  4  );

Tabela zosta│a utworzona.

SQL> insert into t_clob values ( 1, ' xxxx abcd xyz qwerty 354657 [] ' );

1 wiersz zosta│ utworzony.

SQL> declare
  2    i number;
  3  begin
  4    for i in 1..400 loop
  5        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';
  6    end loop;
  7    update t_clob set cl = cl || ' CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text ';
  8    for i in 1..400 loop
  9        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';
 10    end loop;
 11  end;
 12  /

Procedura PL/SQL zosta│a zako˝czona pomyťlnie.

SQL> commit;

Zatwierdzanie zosta│o uko˝czone.
SQL> select length( cl ) from t_clob;

LENGTH(CL)
----------
     25717

SQL> select dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) from t_clob;

DBMS_LOB.INSTR(CL,'NEW.PRODUCT_NO=[')
-------------------------------------
                                12849

SQL> select dbms_lob.substr( cl, 5,dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) + length( 'NEW.PRODUCT_NO=[') ) new_product
  2  from t_clob;

NEW_PRODUCT
--------------------------------------------------------------------------------
T9856

这篇关于在Oracle Clob列中搜索特定的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！