17

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

xMilos
  • 1,519
  • 4
  • 21
  • 36

4 Answers4

32

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%';
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 2
    first worked, the second is not working had tried that aproach also in past, not sure why but i think the second looks only like to_char first 4000 chars – xMilos May 17 '17 at 12:54
  • Can you please tell how can search for a string which doesn't exist in a clob? – user3384231 Dec 13 '18 at 06:36
  • @user3384231 Please read the documentation for `DBMS_LOB.INSTR()` linked in the answer. It tells you the return value if the pattern is not found and you can just use that as a filter rather than `> 0` for when the pattern is found. – MT0 Dec 13 '18 at 08:40
  • @MT0: thank you. When it is = 0 that searches for the pattern not found. – user3384231 Dec 13 '18 at 11:58
5

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.

alan9uo
  • 1,011
  • 1
  • 11
  • 17
  • This is a tiny sample size; there is a significant variance in the timings for both answers; and the mean is equal within a tiny margin (which is significantly less than the variance in the results). Therefore the conclusion from this should be that there is not enough data and you cannot conclude with any certainty if one method is more performant than the other. Just use whichever method you find easiest to comprehend/maintain/document as the performance appears to be roughly equal. – MT0 Apr 03 '23 at 13:23
0

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
Srdjan
  • 121
  • 5
0

USE DBMS_LOB.COMPARE()

SELECT *
FROM   your_table
WHERE  DBMS_LOB.COMPARE( clob_column, 'string to match' ) = 0;

Return Value INTEGER: 0 if the comparison succeeds, nonzero if not.

mike
  • 2,149
  • 20
  • 29