Search for a particular string in Oracle clob column

Question

How to get a particular string from a clob column   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   I would like to search for string NEW PRODUCT NO from column product details  I have tried as  select   from my table where dbms lob instr product details  NEW PRODUCT NO   gt  1   The above fetches full text from my table   Any help is highly appreciable   Regards

User · Answer

Below code can be used to search a particular string in Oracle clob column

select *  
from RLOS_BINARY_BP
where dbms_lob.instr(DED_ENQ_XML,'2003960067') > 0;

where RLOS_BINARY_BP is table name and DED_ENQ_XML is column name (with datatype as CLOB) of Oracle database.

User · Answer

You can use the way like  Florin Ghita has suggested  But remember dbms lob substr has a limit of 4000 characters in the function For example   dbms lob substr clob value column 4000 1   Otherwise you will find  ORA-22835  buffer too small  You can also use the other sql way   SELECT   FROM   your table WHERE  clob value column LIKE   your string     Note   There are performance problems associated with both the above ways like causing Full Table Scans  so please consider about Oracle Text Indexes as well  https   docs oracle com en database oracle oracle-database 12 2 ccapp indexing-with-oracle-text html

User · Answer

Use dbms lob instr and dbms lob substr  just like regular InStr and SubstStr functions  Look at simple example   SQL gt  create table t clob    2    id number    3    cl clob   4      Tabela zosta  a utworzona   SQL gt  insert into t clob values   1    xxxx abcd xyz qwerty 354657          1 wiersz zosta   utworzony   SQL gt  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 pomytlnie   SQL gt  commit   Zatwierdzanie zosta  o uko czone  SQL gt  select length  cl   from t clob   LENGTH CL  ----------      25717  SQL gt  select dbms lob instr  cl   NEW PRODUCT NO      from t clob   DBMS LOB INSTR CL  NEW PRODUCT NO     -------------------------------------                                 12849  SQL gt  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

User · Answer

ok  you may use substr in correlation to instr to find the starting position of your string  select    dbms lob substr         product details          length  NEW PRODUCT NO    --amount        dbms lob instr product details  NEW PRODUCT NO   --offset           from my table where dbms lob instr product details  NEW PRODUCT NO   gt  1

User · Answer

You can just CAST your CLOB value into a VARCHAR value and make your querie like a

User · Answer

select     from TABLE NAME where dbms lob instr COLUMNNAME  searchtext    gt  0

[sql] Search for a particular string in Oracle clob column

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to clob