Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

case statement and like operator in pl/sql

Posted on 2008-06-20
4
4,821 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
ID: 21831481
but this is not working ' % ' .......wild card.  

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

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21831618
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
ID: 21831672
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21831722
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 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