check if clob contains string oracle

user_6170711 0
Viewed 46832

currently i have query with this code to_char(CLOB_COLUM) like %s but the following wont work for very big clob. Is there another solution to check if this column contains some string. Using oracle 11.2.0.4.0

clob oracle sql
3 Answers

Base on MT0's answer. I test which way is efficient.

The CLOB Column length is 155018 and search for 32 length string.

Here is 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 time of INSTR is 0.797.

The average time of LIKE is 0.823.

user_63990740

If you want to see the column's value and Oracle returns ORA-22835 (buffer too small) for WHERE clob_column LIKE '%string to match%', then you should to apply some workaround.

The combination of DBMS_LOB.instr and DBMS_LOB.substr could be a solution. See e.g. this Stackoverflow tip. 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
user_11058990
Related