Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 571
megloff Avatar asked Jul 11 '16 07:07

megloff


People also ask

Can we put multiple condition in CASE statement?

You can evaluate multiple conditions in the CASE statement.

How do you write a CASE statement in SELECT query in Oracle?

Your SQL statement would look as follows: SELECT table_name, CASE owner WHEN 'SYS' THEN 'The owner is SYS' WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END FROM all_tables; With the ELSE clause omitted, if no condition was found to be true, the CASE statement would return NULL.

Can we use case when in WHERE clause?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.


2 Answers

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
like image 115
MT0 Avatar answered Oct 26 '22 18:10

MT0


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.

like image 34
Aleksej Avatar answered Oct 26 '22 18:10

Aleksej