Solved

case statement and like operator in pl/sql

Posted on 2008-06-20
4
4,653 Views
Last Modified: 2013-12-19
hai !
    the code segment is as follows:

DECLARE
   x   VARCHAR2 (20) := 'bull dog';
   y   VARCHAR2 (20);
BEGIN
   y := CASE x
          WHEN  x 'cat%'
             THEN 'good'
          WHEN '%dog'
             THEN 'too good'
          else
             'bad'
       END;
   DBMS_OUTPUT.put_line (y);
END;
/

but this is now working ' % ' .......wild card.  How to implement this ?
0
Comment
Question by:vishali_vishu
  • 2
  • 2
4 Comments
 
LVL 1

Author Comment

by:vishali_vishu
Comment Utility
but this is not working ' % ' .......wild card.  

How to implement this ? I mean the wild cards with in case...when statements ?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
that's because CASE WHEN is using = and not LIKE
DECLARE
   x   VARCHAR2 (20) := 'bull dog';
   y   VARCHAR2 (20);
BEGIN
   y := CASE WHEN  x  LIKE 'cat%'
             THEN 'good'
          WHEN x LIKE '%dog'
             THEN 'too good'
          else
             'bad'
       END;
   DBMS_OUTPUT.put_line (y);
END;
/

Open in new window

0
 
LVL 1

Author Comment

by:vishali_vishu
Comment Utility
angelIII:

I tried that way and it is working.

can't i do some change to my above code to reflect the wildcards ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
as I said:
CASE x WHEN y
will use = and not LIKE. ie would be the same as
CASE WHEN x = y

if you want to use wildcards, it has to be like this:
CASE WHEN x LIKE y
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now