Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

querying single quote using contains

970108Apr 23 2014 — edited Apr 23 2014

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 21 2014
Added on Apr 23 2014
#index, #search, #sql
3 comments
1,255 views