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?