Oracle text search with special character

Question

I haved index as below;

create index cidx_column1 on MY_TABLE(column1) indextype is ctxsys.CTXCAT;

SELECT
from 
  MY_TABLE
WHERE catsearch(column1, '$*', null)  > 0 and taken > 
to_date('01/01/2020','dd/mm/yyyy')

I have a column that can have special character. How do i list all the data that has special character like '$' sign?. Above statement is returning nothing and i can confirm that "column1" has a text $50 and so on.

I want to use oracle text based search not like operator.

Your help or any suggestion in this regard is highly appreciated.

Thanks Ravan

Answer 1

Please use below query. You can use LIKE or INSTR

SELECT
from 
  MY_TABLE
WHERE column1 like '%$%' and taken > 
to_date('01/01/2020','dd/mm/yyyy');

or

SELECT
from 
MY_TABLE
WHERE instr(column1, '$') > 0 and taken > 
to_date('01/01/2020','dd/mm/yyyy');