Oracle CONTAINS query not returning results

Question

I have a table called products with the following schema and data:

|   product_id   |   name   |   description   |   price   |   location   |
|     NUMBER     | VARCHAR2 |       CLOB      |NUMBER(9,2)| SDO_GEOMETRY |
--------------------------------------------------------------------------
|      27        | Nexus 4  |  Android phone  |    160    |     null     |

When I issue a SELECT * FROM products; query, I get the data back. All is well. But I want to be able to get results back using CONTAINS() in a where, like this:

SELECT "PRODUCT_ID", "NAME", "DESCRIPTION", "PRICE"
FROM "PRODUCTS"
WHERE CONTAINS("NAME", 'nexus') > 0;

However I'm getting no results back. The same thing happens when I change nexus to Nexus or Nexus 4. I thought it might be something to do with name being a resolved word, but the same thing happens with the description column.

Answer 1

Turns out this is because I had two text indexes on the same table, for name and description. I removed the one for description and it worked.

Answer 2

If you don't use mixed/lower case table and column names, all of the " characters can be removed in your query ...

Answer 3

SELECT product_id, name, description, price FROM product WHERE CONTAINS(name, '%Nexus%') > 0;