megloff megloff - 4 months ago 13
SQL Question

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

MT0 MT0
Answer

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
Comments