CODEWITHSUNDEEP
sqloracle-database
simplify_life
simplify_life

Reputation: 405

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)

Upvotes: 0

Views: 61

Answers (3)

MT0
MT0

Reputation: 168041

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want either value, you would use:

where CONTAINS(table111.colname, 'RETAIL|CONSOL' , 1) > 0

If you want both:

where CONTAINS(table111.colname, 'RETAIL&CONSOL' , 1) > 0

You should pass the value in with the operator you want, instead of ,.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142778

It would be probably simpler if you said which result you want. The way I understood it, maybe this helps:

select *
from table111
where instr(colname, 'RETAIL_MCH') > 0
   or instr(colname, 'CONSOL_MCH') > 0;

OR might need to be substituted by AND (depending on what you want).

Upvotes: 0

Related Questions