oracle sql - select statement with multiple "case when" and check for contains text

Question

I look for a working example where I can use mutliple when case statment wihch check to verify if a specific text is contained: e.g.

SELECT 
  ID,
  NAME,
  (SELECT 
      (Case when Contains(Descr,"Test") Then "contains Test" 
        when Contains(Descr, "Other") Then "contains Other"
        Else "No Match" End) From DESCRIPTION
       where item_id = id
  ) as "Match"
  From Item

Typewriting

What exactly is your question? Else "No Match" is invalid SQL (unless you have a column that is named No Match). Also contains()` is an Oracle Full Text function and requires a different syntax then the one you are using. Are you sure you are using Oracle? – Idolatrous

The contains operator seems not to be known, I get an error – Typewriting

ok, which other function than "contains" can I use to look for a substring match? – Typewriting

docs.oracle.com/database/121/SQLRF/functions089.htm#SQLRF00651 – Idolatrous

Answer 1

In Oracle string literals need to be surrounded in single quotes.

To find a sub-string match you can either use LIKE:

SELECT  ID,
        NAME,
        CASE WHEN Descr LIKE '%Test%'  THEN 'Contains Test'
             WHEN Descr LIKE '%Other%' THEN 'Contains Other'
             ELSE 'No Match'
        END AS Match
FROM    Item i
        LEFT OUTER JOIN
        Description d
        ON i.id = d.item_id

or INSTR():

SELECT  ID,
        NAME,
        CASE WHEN INSTR( Descr, 'Test' ) > 0  THEN 'Contains Test'
             WHEN INSTR( Descr, 'Other' ) > 0 THEN 'Contains Other'
             ELSE 'No Match'
        END AS Match
FROM    Item i
        LEFT OUTER JOIN
        Description d
        ON i.id = d.item_id

or REGEXP_LIKE():

SELECT  ID,
        NAME,
        CASE WHEN REGEXP_LIKE( Descr, 'Test' )  THEN 'Contains Test'
             WHEN REGEXP_LIKE( Descr, 'Other' ) THEN 'Contains Other'
             ELSE 'No Match'
        END AS Match
FROM    Item i
        LEFT OUTER JOIN
        Description d
        ON i.id = d.item_id

Malonis

Answer 2

Use INSTR function instead of Contains

Heuer

Answer 3

You probably need something like this:

with Item(id, name, descr) as
(
 select 'id1', 'name1', 'description containing Test' from dual union all
 select 'id2', 'name2', 'description containing Others' from dual union all
 select 'id3', 'name3', 'description containing nothing interesting' from dual
)
SELECT 
    ID,
    NAME,
    descr,
    case
        when instr(Descr, 'Test') != 0 then 'contains Test' 
        when instr(Descr, 'Other')!= 0 then 'contains Other'
        Else 'No Match'
    End as "Match"
From Item

Using INSTR is only one of the possible solutions; you may use LIKE, regular expressions, etc and different ways of writing the same query; I believe this is plain enough to be quite self-explanatory.

Alti

Answer 4

you can try this:

SELECT 
  1,
  2,
  (SELECT 
      (
      Case 
        when     'contains Test' like  '%Test%'   
                              Then 'contains Test' 
        when     'contains Test' like  '%Other%'  
                              Then 'contains Other'
        Else 'No Match'
      End
      ) From dual
       where 1 = 1
  ) as "Match"
  From dual

you can use like func

Rockett