0 Replies Latest reply: May 5, 2014 10:05 PM by 970108 RSS

    oracle text search-querying single quote

    970108

      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 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

       

      Thanks

      Sai