Forum Stats

  • 3,782,598 Users
  • 2,254,670 Discussions
  • 7,880,132 Comments

Discussions

querying single quote using contains

970108
970108 Member Posts: 7
edited Apr 23, 2014 8:05AM in SQL & PL/SQL

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 to 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

Tagged:

Answers

This discussion has been closed.