oracle sql contain

I have table with values in columns like

colname
TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH
RETAIL_MCH
RETAIL_MCH,CONSOL_MCH
CONSOL_MCH
OTA_MCH

I need to run query to fetch all rows contains RETAIL_MCH or CONSOL_MCH. if i run query below i get result as below

select * from table111  where 
 (CONTAINS(table111.colname, 'RETAIL,CONSOL' , 1) > 0)

TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH
RETAIL_MCH
RETAIL_MCH,CONSOL_MCH
CONSOL_MCH

but I need to exact search including underscore “_”

select * from table111 where (CONTAINS(table111.colname, ‘RETAIL_MCH,CONSOL_MCH’ , 1) > 0)

Answer

CONTAINS is an Oracle text function, you can escape the underscore:

SELECT *
FROM   table111
WHERE  CONTAINS( colname, 'RETAIL_MCH,CONSOL_MCH', 1 ) > 0

Or, if you want to pass the string in unescaped then you could use REPLACE to add the escape characters:

SELECT *
FROM   table111
WHERE  CONTAINS( colname, REPLACE ( 'RETAIL_MCH,CONSOL_MCH', '_', '_' ), 1 ) > 0

Which, for the sample data:

CREATE TABLE table111 ( colname ) AS
SELECT 'TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH' FROM DUAL UNION ALL
SELECT 'RETAIL_MCH' FROM DUAL UNION ALL
SELECT 'RETAIL_MCH,CONSOL_MCH' FROM DUAL UNION ALL
SELECT 'CONSOL_MCH' FROM DUAL UNION ALL
SELECT 'OTA_MCH' FROM DUAL;

CREATE INDEX table111__colname__textidx ON table111(colname) INDEXTYPE IS CTXSYS.CONTEXT;

Outputs:

| COLNAME                                                              |
| :------------------------------------------------------------------- |
| TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH |
| RETAIL_MCH                                                           |
| RETAIL_MCH,CONSOL_MCH                                                |
| CONSOL_MCH                                                           |

db<>fiddle here