FAQ-1347 How can I escape the ? and * characters in my DBMS LIKE query?


FAQ-1347 How can I escape the ? and * characters in my DBMS LIKE query?

» Symbian OS » Developer Knowledgebase » FAQ-1347

Classification:	C++	Category:	DBMS
Created:	12/06/2005	Modified:	12/16/2005
Number:	FAQ-1347
Platform:	Symbian OS v6.1, Symbian OS v7.0, Symbian OS v7.0s, Symbian OS v8.0, Symbian OS v8.0a, Symbian OS v8.0b, Symbian OS v8.1a, Symbian OS v8.1b

Question:
When I specify a ? or * characters in a TDbQuery of RDbView and RDbRowSet these are interpreted as wildcard values. How can I escape the ? and * characters so that I can match exactly on these characters

Answer:
You cannot match directly on '*' or '?' characters because DBMS does not support an escape character.

It is possible to work around this limitation by matching on '?' (any character) for either of these literal characters and then do tests on all the rows retrieved from RDbView to determine if they are an exact match.

For example, consider the case where you need to find rows where 'A?C' (or any arbitrary user-entered text) is contained within the database text. In this case you would first use SELECT * FROM foo WHERE bar LIKE '*A?C*'to get the rows that match the DBMS wildcard search. You could then use TDesC::Find("A?C") on the 'bar' column in the retrieved rows to identify those which exactly match the descriptor substring search.

Note that you cannot use TDesC::Match() for this post-processing, because this does not support escaping of the '?' wildcard character (DBMS text comparison is based on this method, hence the lack of support for escaped characters in the higher level API).