Unanswered: DB2 LUW Text Search - search using special characters
I have installed DB2 LUW 9.7.4 with Text Search option on Windows box and I'm currently 'struggling' with full text search queries containing special characters, especially "_" (underscore). I'm searching agains XML structure inside XML column using xpath.
The "FILENAME" XML tag contains records like "abcdefg_h.pdf".
This is my query:
SELECT * FROM MY_XML_TABLE WHERE contains(DOC_XML, '@xpath:''/DOCUMENT/FILENAME[. contains("abcdefg\_h*") ]''')=1
Here's the result:
Error occurred during text search processing on server "localhost" using index "SCHEMANAME.TXTIND_DOCXML". The error message is ""IQQS0032E The query cannot be proces".. SQLCODE=-20423, SQLSTATE=38H10, DRIVER=3.53.71
From the db2diag.log I can see that the command syntax used in wrong:
"IQQS0032E The query cannot be processed because it has incorrect syntax.
Causes of the problem:
IQQG0020E com.ibm.siapi.SiapiException: Message0: SEVERITY_ERROR: Message ID:  Resource Bundle:  Message Text: 
IQQG0020E com.ibm.es.nuvo.search.query.parser.TokenMgrError: Lexical error at line 1, column 48. Encountered: "_" (95), after : "\'/DOCUMENT/FILENAME[. contains(\"abcdefg\\""
As you can see, I use "\" to escape "_" character (according to TS documentation) and wildcard (*) search, but this obviously doesn't work as I expected (I also get the same error using the term without "*" (e.g. "abcdefg\_h.pdf")). Query with "abcdefg_h.pdf" works fine.
What I'm I doing wrong?
I would really appreciate some help on this issue.
Thank you for your reply. At first I've also thought about the ESCAPE keyword, but I believe this is only valid for NSE, but not for DB2 Text Search. The only way, as I see, is to use "\" to escape special chars from search term.
OK, I see what doc says. But still, if I perform a query like the following one, I get the result(s) correctly:
SELECT * FROM MY_XML_TABLE WHERE contains(DOC_XML, '@xpath:''/DOCUMENT/FILENAME[ contains("abcdefg*") ]''')=1;
Only when underscore "_" char is in play, the query doesn't return anything. Here's the example:
SELECT * FROM DOCUMENTS_XML WHERE contains(DOC_XML, '@xpath:''/DOCUMENT/FILENAME[ contains("abcdefg_*") ]''')=1;