var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: ESCAPE clause in CONTAINS
Hi. I'm using Oracle 220.127.116.11.0. I'm trying to do a search on a VARCHAR2 text field using the CONTAINS clause. I'm trying to search for the string "9-10", as follows:
SELECT * FROM myTable WHERE CONTAINS(column1, '9-10') > 0;
This query only returns rows that have a word starting with '9', it ignores the rest.
I know that there is no default escape sequence in Oracle and that the minus sign (-) is used for continuing commands on another line.
I've also tried:
SELECT * FROM myTable WHERE CONTAINS(column1, '9\-10') > 0 ESCAPE '\';
but this doesn't work either - it returns an error.
How would I be able to look for the string '9-10' using the CONTAINS clause (not LIKE)?
Would using a double quote help ???
It doesn't make a difference if I enclose the word in '' or "".
Re: ESCAPE clause in CONTAINS
What if you use INSTR?
SELECT * FROM myTable WHERE INSTR(column1, '9-10') > 0;
That won't work either. We have a context sensitive index on the column. We're appending '%' to the end of each word since it's a partial word search.
If you inputted 'NAVY', the search would be
contains(column1, 'NAVY%') > 0
have u set the escape on.
SET ESCAPE ON. Try out this after executing this.
I'm not running the queries in SQL*PLUS. I'm running them through my Java application. The SET ESCAPE command is for SQL*PLUS.
Any other ideas?