Oracle 18c

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