1 Reply Latest reply: Jul 5, 2013 6:32 PM by Solomon Yakobson RSS

    Include like clause value in result list

    Mac_

      I have multiple like clauses on same column in my query. Is is possible to include the like clause value in result set?

      For example

       

      SELECT * FROM address ADDR WHERE

      addr.line LIKE '%ONE%'

      OR addr.line LIKE '%TWO%'

      OR addr.line LIKE '%THREE%'

      OR addr.line LIKE '%FOUR%';

       

      The resultset contains all the addresses including ONE,TWO,THREE,FOUR. Now i would like to see the result set like

       

      Address1ONE | ONE

      Address2TWO | TWO

      THREEaddr   | THREE

       

      any clue?

      -R

        • 1. Re: Include like clause value in result list
          Solomon Yakobson

          Use CASE:

           

          SELECT  addr.line,

                  CASE

                    WHEN addr.line LIKE '%ONE%' THEN 'ONE'

                    WHEN addr.line LIKE '%TWO%' THEN 'TWO'

                    WHEN addr.line LIKE '%THREE%' THEN 'THREE'

                    ELSE 'FOUR'

                  END

            FROM  address ADDR

            WHERE addr.line LIKE '%ONE%'

               OR addr.line LIKE '%TWO%'

               OR addr.line LIKE '%THREE%'

               OR addr.line LIKE '%FOUR%'

          /

           

          SY.