Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: ESCAPE clause in CONTAINS

    Hi. I'm using Oracle 8.1.7.0.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)?

    Thanks,
    - Dylan

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Would using a double quote help ???

    "9-10"

    Gregg

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    It doesn't make a difference if I enclose the word in '' or "".

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: ESCAPE clause in CONTAINS

    What if you use INSTR?

    SELECT * FROM myTable WHERE INSTR(column1, '9-10') > 0;

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    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

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi Dilan,

    have u set the escape on.

    SET ESCAPE ON. Try out this after executing this.

    Regards
    Saravanan.R

  7. #7
    Join Date
    Mar 2004
    Posts
    8
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •