By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
5 rows affected
CREATE INDEX table111__colname__textidx ON table111(colname) INDEXTYPE IS CTXSYS.CONTEXT;
SELECT *
FROM table111
WHERE CONTAINS( colname, 'RETAIL\_MCH,CONSOL\_MCH', 1 ) > 0
| COLNAME |
|---|
| TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH |
| RETAIL_MCH |
| RETAIL_MCH,CONSOL_MCH |
| CONSOL_MCH |
SELECT *
FROM table111
WHERE CONTAINS( colname, REPLACE ( 'RETAIL_MCH,CONSOL_MCH', '_', '\_' ), 1 ) > 0
| COLNAME |
|---|
| TMC_MCH,OTA_MCH,CONSOL_MCH,RETAIL_MCH,TOUROP_MCH,SPEC_MCH,QRACTO_MCH |
| RETAIL_MCH |
| RETAIL_MCH,CONSOL_MCH |
| CONSOL_MCH |