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

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

Answer 2

Use INSTR function instead of Contains

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.

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