2 Replies Latest reply on Jul 7, 2020 12:29 PM by Sainaa

    Simplify case?

    Sainaa

      Hello, I have list of names which some of them need write case:

       

      For example highlighted rows should be named as one not two:

       

      case when name = 'Дархан' or name =  'Дархан Дархан' then 'Дархан'

               when .......

      the list will go on.

      The question is do I have to write case for every single names regardless it should be renamed or not?

       

      like case when name = a or name = b then a

             case when name = c then c

             etc...

        • 1. Re: Simplify case?
          BEDE

          I think you may use an update like below:

           

          begin

          for r in (select * from x_table)

          loop

             update x_table  t set

                 new_name_column=r.name_column

             where replace(t.name_column,' ','')=trim(r.name_column)||trim(r.name_column);

          end loop;

          commit;

          end;

           

          This supposing name_column is the column where those names are stored and new_name_column is another column where you store what you finally need to display.

          That's what comes into my mind so far, as to be as general as it can be.

          • 2. Re: Simplify case?
            Frank Kulash

            Hi,

            Sainaa wrote:

             

            Hello, I have list of names which some of them need write case:

            For example highlighted rows should be named as one not two:

             

            case when name = 'Дархан' or name = 'Дархан Дархан' then 'Дархан'

            when .......

            the list will go on.

            The question is do I have to write case for every single names regardless it should be renamed or not?

             

            like case when name = a or name = b then a

            case when name = c then c

            etc...

            It's not clear what you want to do.

             

            Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

            Explain, using specific examples, how you get those results from that data.

            Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

            See the forum FAQ: Re: 2. How do I ask a question on the forums?

             

            If you want to eliminate duplicates, then use SELECT DISTINCT.

            If sometimes two (or more) strings really are different, but you want to treat them as duplicates, then you can use a CASE expression similar to what you posted, but only list the ones that need to be renamed., and use ELSE for all the others.

            For example, if you want a list of distinct enames from the scott.emp table, but you want to treat a few names as duplicates of others, then you can do something like this:

            SELECT DISTINCT

                      CASE  ename

                          WHEN  'SMITH'  THEN  'JONES'

                          WHEN  'ALLEN'  THEN  'SAINAA'

                          WHEN  'FUBAR'  THEN  'FOO'

                                         ELSE  ename

                      END  new_name

            FROM      scott.emp

            ORDER BY  new_name

            ;

            Output:

            NEW_NAME

            ----------

            ADAMS

            BLAKE

            CLARK

            FORD

            JAMES

            JONES

            KING

            MARTIN

            MILLER

            SAINAA

            SCOTT

            TURNER

            WARD

            The table contains both 'SMITH' and 'JONES', but the results have only 'JONES'.

            The table odes not contain 'FUBAR'; that doesn't cause any problem.

             

            If you're ever tempted to us a CASE expression that needs lots of WHEN clauses, then maybe you ought to have the mapping stored in a table.