search for the presence of two strings in oracle database in a clob column?

oracle check if string contains letters
how to find multiple occurrences of a string in oracle
oracle instr
regexp_instr in oracle 12c
find string in oracle database
oracle pattern matching
oracle string contains
oracle regexp_like

I have a clob column with a long string.

like "great job done today. good "

I want to retrieve all the records which has both the words great and good .

both the position is not fixed.

It's not clear what "which has both the words great and good " actually means.

If you mean you want to check if the string has both patterns of words, then it is simple as

where lower(col) like '%good%' AND lower(col) like '%great%'

If you mean they should contain true words good and great, then for simpler cases something like should do

where ' ' || lower(col) || ' ' LIKE '% good %' AND ' ' || lower(col) || ' ' LIKE '% great %'

However, this will not match a sentence ending with the word and having a full stop. It gets lengthy if you want to simply use LIKE to handle such scenarios.

REGEXP functions give more flexibility, but could be less performant.

WHERE REGEXP_LIKE ( s, '(^|\W)good(\W|$|\.)') AND REGEXP_LIKE ( s, '(^|\W)great(\W|$|\.)')

This searches for word boundaries, i.e. the word surrounded by non-word characters and start and end including an ending dot. If you want to ignore case, add ,'i' as 3rd argument to it.

Querying with Oracle Text, The following example shows a field section search against a CTXCAT index using If your file is text in the database character set, then you can create a BFILE and load it to a CLOB using You can enter word queries with both CONTAINS and CATSEARCH SQL operators. Presence of LOB columns in your base table. Hi, We got file exported data in form of csv including clob column from source oracle DB and need to load the data into target oracle table using sql loader. Database. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production

I think this is the basic logic you want:

where lower(col) like '%good%' and lower(col) like '%great%'

In clobs, you can't quite use like:

where dbms_lob.instr(col, 'good') and dbms_log.instr(col, 'great')

Pattern-matching Conditions, Database SQL Language Reference. Contents In this syntax: char1 is a character expression, such as a character column, called the search value. char2 is a  Oracle supports full-text search on documents that are managed by the Oracle Database. If your documents are XML, then you can use the XML structure of the document to restrict the full-text search. For example, you may want to find all purchase orders that contain the word "electric" using full-text search.

Try this and let me know if it works for you.

SELECT *
FROM clob_table
WHERE DBMS_LOB.INSTR(clob_text,'great')>0 
      AND DBMS_LOB.INSTR(clob_text,'good')>0;

Remember put the names of your table and columns.

Here my results,

regexp_count, Database SQL Language Reference It is commonly a character column and can be of any of the datatypes CHAR , VARCHAR2 , NCHAR After finding the first occurrence of pattern , the database searches for a second occurrence beginning If you specify multiple contradictory values, then Oracle uses the last value. Should I wait until the MatchPoint is built into Oracle database, to migrate our Endeca search engine to Oracle? --Endeca is a light weight distribution TEXT search engine, we got 70 search servers now.

regexp_instr, It is commonly a character column and can be of any of the datatypes CHAR is different from the datatype of source_char , Oracle Database converts pattern to the The default is 1, meaning that Oracle searches for the first occurrence of pattern . If you specify multiple contradictory values, Oracle uses the last value. The XMLType column itself becomes a virtual column over this hidden CLOB column. It is not possible to directly access the CLOB column; however, you can set the storage characteristics for the column using the XMLType storage clause. In Structured storage (in tables and views). Structured storage maintains DOM (Document Object Model) fidelity.

Searching XML Data with Oracle Text, XML text is aVARCHAR2 or CLOB type in an Oracle9i database table with character semantics. Oracle Text can also All XML data here is represented using US-ASCII, a 7 bit character set. Specifies the query expression that defines your search in column. label Using HASPATH Operator for Path Existence Searching  "Hi Folks, I am trying to locate a particular character in a CLOB field via INSTR function. However, everytime INSTR returns incorrect results. I am converting the CLOB column to varchar before I apply the INSTR function - but still in vain..

Oracle / PLSQL: REGEXP_INSTR Function, The Oracle / PLSQL REGEXP_INSTR function is an extension of the INSTR function. string: The string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. pattern Oracle 12c, Oracle 11g, Oracle 10g REGEXP_INSTR function with a table column and search for multiple characters. Hi everybody, I have a CLOB filed, in a table, which contains N floating point numbers (e.g. 130,406325;131,394785;113,24137; ). I'd like to be able to store such N values in a temporary Oracle data structure (e.g. collection), in order to use the whole set of values as if they were available in a view.

Comments
  • So do you want a solution which would match "My goodness! I didn't know old Jenkins read Greats at Oxford." ?
  • Please check my answer and accept the answer if you think it works for you or give relevant feedback. Please read: stackoverflow.com/help/someone-answers to understand why it's important.
  • I think it is clear that the OP wants rows where the column contains both strings. So whatever approach we take a correct solution must be a Boolean and not an or.