Clever Geek Handbook

Check if clob contains oracle string

Currently I have a request with this code to_char(CLOB_COLUM) like %s

, but the following work doesn't work for a very large clob. Is there another solution to check if this column contains any row. Using oracle 11.2.0.4.0

+6


source to share


3 answers


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


source


Basics of MT0's answer. I am checking which path is efficient.

The CLOB column length is 155018 and the lookup is 32 strings of length .

Here's my test.

| INSTR  | LIKE  |
|:-------|------:|
| 0.857  |0.539  |
| 0.127  |0.179  |
| 1.635  |0.534  |
| 0.511  |0.818  |
| 0.429  |1.038  |
| 1.586  |0.772  |
| 0.461  |0.172  |
| 0.126  |1.379  |
| 1.068  |1.088  |
| 1.637  |1.169  |
| 0.5    |0.443  |
| 0.674  |0.432  |
| 1.201  |0.135  |
| 0.419  |2.057  |
| 0.731  |0.462  |
| 0.787  |1.956  |


enter image description here

The average INSTR is 0.797.

The average LIKE time is 0.823.

+2


source


If you want to see the value of a column and Oracle returns ORA-22835

(too small a buffer) for WHERE clob_column LIKE '%string to match%'

, then you should apply some workaround.

A combination of DBMS_LOB.instr

and DBMS_LOB.substr

might be the solution. See fooobar.com/questions/162943 / ... for example . So in your case:

SELECT DBMS_LOB.substr(your_clob_column, DBMS_LOB.instr(your_clob_column,'string to match'), 1) AS Text
FROM your_table
WHERE DBMS_LOB.instr(your_clob_column, 'string to match') > 0
0


source






More articles:


All Articles