CODEWITHSUNDEEP
sqldb2
arnoldssss
arnoldssss

Reputation: 488

How to find two strings in a CLOB column?

Ive tried many queries to find... just one word and I can´t even make that.

Its a DB2 database Im using com.ibm.db2.jcc.DB2Driver This brings me info:

select *
from JL_ENR
where id_ws = '002'
and dc_dy_bsn = '2014-08-25'
and ai_trn = 2331

the JL_TPE column is the CLOB column where I want to find two strings in that search result ( and dc_dy_bsn = '2014-08-25' and ai_trn = 2331 ).

So first I tried with one:

select 
   dbms_lob.substr(clob_column,dbms_lob_instr(JL_TPE,'CEMENTO'),1)
from 
   JL_ENR 
where 
   dbms_lob.instr(JL_TPE,'CEMENTO')>0;

didnt work

SELECT * FROM JL_ENR WHERE dbms_lob.instr(JL_TPE,'CEMENTO')>0
and ai_trn = 2331
and dc_dy_bsn = '2014-08-25'

didnt work

Select *
From JL_ENR
Where NOT 
DBMS_LOB.INSTR(JL_TPE, 'CEMENTO', 1, 1) = 0;

didn´t work

Could someone explain me how to find two strings please? Or a tutorial link where it is explained how to make it work...

Thanks.

Upvotes: 0

Views: 4404

Answers (2)

Keenan Stewart
Keenan Stewart

Reputation: 634

I had to search for a specific value in the where clause. I used TEXTBLOB LIKE '%Search value%' and it worked! This was for db2 in a CLOB(536870912) column.

Upvotes: 0

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Can you provide some sample data and the version you are using? Your example should work (tested on v10.5.0.1):

db2 "create table test ( x int, y clob(1M) )"
db2 "insert into test (x,y) values (1,cast('The string to find is CEMENTO, how do we do that?')"
db2 "insert into test (x,y) values (2,cast('The string to find is CEMENT, how do we do that?' as clob))"

db2 "select x, DBMS_LOB.INSTR(y, 'CEMENTO', 1) from test where DBMS_LOB.INSTR(y, 'CEMENTO', 1) > 0"

X           2          
----------- -----------
          1          23

  1 record(s) selected.

Upvotes: 2

Related Questions