title: Oracle text query - contains and special characters
body: <p>We are using an oracle text query to perform a search of a table based on user input.</p>
  
  <p>So if the user types in "blue sky" - we want to search for each word on an OR basis, so we do - <code>where contains(columnname,'blue or sky',1)&gt;0</code></p>
  
  <p>so we take the user input and replace spaces with the word <code>'or'</code> before passing to the query.</p>
  
  <p>This works fine and we are able to order descending on the score to give us the most relevant entries first.</p>
  
  <p>However we have then had problem with <code>'special' characters</code> - it started with a comma but then I found in the <a href="http://docs.oracle.com/cd/B28359_01/text.111/b28304/cqspcl.htm#CCREF2093" rel="nofollow">documentation</a> there are loads of them.</p>
  
  <p>So we wrote some code which detected each <code>'special' character</code> and prefixed it with an <code>escape '\' character</code>. This also works ok. But then there are also the reserved words, eg AND - so if a user types in <code>'jack and jill'</code> - we convert to <code>'jack or and or jill'</code> and this gives a text query parser syntax error because of the use of the word 'and' - so then trying to cater for those too but having to try to determine where they are prefixed and suffixed with spaces so as not to pick out 'handy' for example. Except of course it could be the first or last word.....grrr there must be an easier way to do this.....</p>
  
  <p>Then I read up on the <code>{} braces option</code> so this escapes the whole string. </p>
  
  <p><strong>Question</strong> - can I just do this, even if there are no special characters in the string?</p>
  
  <p>Also I can't see how this caters for the OR functionality we need for each word - so if I do contains(<code>columnname,'{jack or xxxxx}',1) &gt; 0</code>) it does not return anything.</p>
  
  <p>Any advice will be greatfully received, thanks!</p>