CODEWITHSUNDEEP
sqloracle-databaseclob
xMilos
xMilos

Reputation: 2018

check if clob contains string oracle

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

Upvotes: 22

Views: 72745

Answers (4)

mike
mike

Reputation: 2308

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.

Upvotes: 1

Srdjan
Srdjan

Reputation: 163

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

Upvotes: 0

alan9uo
alan9uo

Reputation: 1131

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.

Upvotes: 5

MT0
MT0

Reputation: 167962

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

Upvotes: 37

Related Questions