CODEWITHSUNDEEP
oracle-databasetextescapingcontains
kanadianDri3
kanadianDri3

Reputation: 371

searching a text column that contains query operator words

I've been going around this issue without any luck so far. I have a basic query that is searching if the word 'or' or 'and' is contained in a column.

For example :

SELECT * FROM MyTable
WHERE CONTAINS(title, 'and', 1) > 0

Doing this throws an oracle error : SQL Error: ORA-29902: error in executing ODCIIndexStart() routine

Searched this website and the web in general to find a solution

Found these 2 threads that I gave me a hint on how to escape this keyword

1, 2

But it doesn't work.

I know I have rows where the titles are (for example) :

If I stick two '%' (pourcentage) at both ends of the word 'and' (giving this query):

SELECT * FROM MyTable
WHERE CONTAINS(title, '%and%', 1) > 0

I have no errors, but in my result, I get words that contain the string 'and' (like QOANDOA). Note that I can add a single '%' and it will also work

But it's not what I'm looking for :(

Escaping using {} returns me an empty result. Same goes for using '/'

Any ideas on how to resolve this?

Upvotes: 0

Views: 605

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You should create a context index, and regularly synchronize that to keep up date with DMLs applied to table which owns the index. This could be done by using ctx_ddl.sync_index :

SQL> create index idx_title on mytable(title) indextype is ctxsys.context;

Index created.

SQL> exec ctx_ddl.sync_index('idx_title'); -- there are lots of arguments optional, in this case i left the other args. as default

PL/SQL procedure successfully completed.

SQL> SELECT * FROM MyTable;   

TITLE
--------------
CPAND AND SLAF
QOAND AND GI
QOANDOA

SQL> SELECT * FROM MyTable
WHERE CONTAINS(title, '%AND%', 1) > 0;  2  

TITLE
--------------
CPAND AND SLAF
QOAND AND GI

Upvotes: 1

Related Questions