I'm using the Oracle Text contains function, but it sometimes doesn't work properly. It returns 0 (text not found) when the text is actually there.
select e.* , contains(e.emp_name, '%Jones%') as cont_val
from emp_table e
where e.emp_name = 'Jones'
Returns a row with cont_val = 0 (which means text not found). Results for %Jones% in the contains function in the where clause return no rows. This is a sporadic problem that only occurs for some rows in our table. We're using Oracle 10.2.0.1.0.
Any ideas what's going on?