0 Replies Latest reply on May 6, 2014 3:05 AM by 970108

    oracle text search-querying single quote


      Hi all,


      I have a table t1 with a clob column xml_keywords which stores names. Proper index on xml_keywords (ctxsys.context) was created.


      Now I am running the below query


      1) select * from t1 where contains(xml_keywords,'la''st',1)>0


      returns row with xml_keywords having la'st_name, first_name


      2) select * from t1 where contains(xml_keywords,'la''st%',1)>0


      returns no rows.


      Why the difference? and how can we escape the apostrophe and also search with a wild card character?



      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production