Search for a Particular String in Oracle Clob Column

Search for a particular string in Oracle clob column

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

check if clob contains string oracle

You can use 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%';

Oracle SQL : Select Query : search if clob Contains a string with pattern matching

Generic string functions for parsing JSON inputs are dangerous - you will get false positives, for example, when something that looks like a JSON object is in fact embedded in a string value. (Illustrated by ID = 101 in my example below.)

The ideal scenario is that you are using Oracle 19 or higher; in that case you can use a simple call to json_exists as illustrated below. In the sample table I create, the first JSON string does not contain a member named apple. In the second row, the string does contain a member apple but the value is null. The first query I show (looking for all JSON with an apple member) will include this row in the output. The last query is what you need: it adds a filter so that a JSON string must include at least one apple member with non-null value (regardless of whether it also includes other members named apple, possibly with null value).

create table sample_data
( id      number primary key
, colname clob   check (colname is json)
);

insert into sample_data
    values (101, '{name:"Chen", age:83, values:["{apple:6}", "street"]}');

insert into sample_data
    values (102, '{data: {fruits: [{orange:33}, {apple:null}, {plum:44}]}}');
    
insert into sample_data
    values (103, '[{po:3, "prods":[{"apple":4}, {"banana":null}]},
                   {po:4, "prods":null}]');

Note that I intentionally mixed together quoted and unquoted member names, to verify that the queries below work correctly in all cases. (Remember also that member names in JSON are case sensitive, even in Oracle!)

select id
from   sample_data
where  json_exists(colname, '$..apple')
;

 ID
---
102
103

This is the query you need. Notice the .. in the path (meaning - find an object member named apple anywhere in the JSON) and the filter at the end.

select id
from   sample_data
where  json_exists(colname, '$..apple?(@ != null)')
;

 ID
---
103

How to find a particular string in a clob which has another string as prefix to it

Use DBMS_LOB functions to work with CLOBs. instr() returns the starting offset of the searched pattern in a CLOB. substr() returns a substring, which is n characters from the given offset in a CLOB. Find out more

SQL> select id,
  2        dbms_lob.substr(txt, 30, dbms_lob.instr(txt,'Grading Time Period')+21) as snippet
  3  from t23
  4  where dbms_lob.instr(txt, 'Grading Time Period') > 0:

        ID SNIPPET
---------- ----------
         1 2016 Fall

SQL> 

This is a poor way to store data. Really this should be properly normalised into table columns. But even XML or JSON would be a better way of handling this. Look at it this way. If you wanted to extract the Grading how would you specify the buffer to ensure you grabbed only As Specified? It's possible but it's gnarly.

How to query a CLOB column in Oracle

When getting the substring of a CLOB column and using a query tool that has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size. For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000.

Running the DBMS_LOB.substr command you can also specify the amount of characters you want to return and the offset from which. So using DBMS_LOB.substr(column, 3000) might restrict it to a small enough amount for the buffer.

See oracle documentation for more info on the substr command

    DBMS_LOB.SUBSTR (
       lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
       amount      IN    INTEGER := 32767,
       offset      IN    INTEGER := 1)
      RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

Querying oracle clob column

Yes, it's not allowed (this restriction does not affect CLOBs comparison in PL/SQL)
to use comparison operators like =, !=, <> and so on in SQL statements, when trying
to compare two CLOB columns or CLOB column and a character literal, like you do. To be
able to do such comparison in SQL statements, dbms_lob.compare() function can be used.

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, 'value') = 0

In the above query, the 'value' literal will be implicitly converted to the CLOB data type.
To avoid implicit conversion, the 'value' literal can be explicitly converted to the CLOB
data type using TO_CLOB() function and then pass in to the compare() function:

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, to_clob('value')) = 0

How to find particular sub part of the clob data column ( actually characters are stored) using oracle sql command?

you can use DBMS_LOB.SUBSTR and DBMS_LOB.INSTR functions
in your query you can select only subpart of CLOB as a text and then do what you need with the text as with simple text field

for example:

--create test table from user_tables DDL text
create table test as
select table_name, dbms_metadata.get_ddl('TABLE', table_name) as clob_field
from user_tables

--sample select
 select 
 dbms_lob.substr(clob_field, 17) as sample_first_17_chars
,dbms_lob.substr(clob_field, 6, instr(clob_field,'NUMBER')) as sample_number_text      
  from test
where dbms_lob.instr(clob_field, 'NUMBER') > 0

result

SAMPLE_FIRST_17_CHARS, SAMPLE_NUMBER_TEXT
CREATE TABLE ",NUMBER
CREATE TABLE ",NUMBER
CREATE TABLE ",NUMBER
CREATE TABLE ",NUMBER

Querying Oracle Clob datatype

Hope this examples illustrates clearly what i am trying to explain.

SET SQLBL ON;
SET DEFINE OFF;

CREATE TABLE CLOB_TEST
(
clob_in CLOB
);

INSERT
INTO CLOB_TEST VALUES
  (
    'I am working as a DBA and senior database resource in L&T Infotech in Pune India'
  );

SELECT DBMS_LOB.SUBSTR(CLOB_IN,3000) ot FROM CLOB_TEST;

-----------------------------OUTPUT------------------------------------------

OT
I am working as a DBA and senior database resource in L&T Infotech in Pune India

-----------------------------OUTPUT------------------------------------------

---

Related Topics