3 Replies Latest reply: Jul 3, 2013 3:40 PM by 1013527 RSS

    Like is not working with substr...

    1013527

      Hello,

       

      I am trying to do

      FOR i IN (SELECT grp.group_id

                     FROM ems.groups grp, ems.groups_loc gl, ems.location loc

                    WHERE grp.group_id = gl.group_id

                      AND loc.loc_id = gl.loc_id

                      AND gl.loc_id = i_loc_id

                      AND (UPPER(group_desc) LIKE (UPPER(SUBSTR (location_desc, 3,INSTR(SUBSTR(location_desc,3,LENGTH(location_desc)),',')-1)) || ' IT')

                       OR  UPPER(group_desc) LIKE (UPPER(SUBSTR (location_desc, 3,INSTR(SUBSTR(location_desc,3,LENGTH(location_desc)),',')-1)) || ' FACILITIES' ))

                    ) LOOP

      Here there is data with UPPER(SUBSTR (location_desc, 3,INSTR(SUBSTR(location_desc,3,LENGTH(location_desc)),',')-1)) || ' IT') as well as (UPPER(SUBSTR (location_desc, 3,INSTR(SUBSTR(location_desc,3,LENGTH(location_desc)),',')-1)) || ' FACILITIES' )),

      But it seems to be 'LIKE' is not working.

      Can please anybody help me with this problem.

       

      Thanks.

        • 1. Re: Like is not working with substr...
          Greg Spall

          Provide some sample data ... show it not working - it'll make it easier for us to help you.

           

          At a glance, I don't see any need for LIKE . you have no wild cards in there.

           

          So perhaps you just need to add a || '%' to the end of your string?

           

          No idea though, no clue what your data looks like ..

           

          https://forums.oracle.com/thread/2176552

          • 2. Re: Like is not working with substr...
            1013527

            Sorry, for insufficient information.

            here i have location desc as 39 Herndon, VA, 11 Jacksonville, FL, 48 Pittsburgh, PA. From these description i have to get Herndon, Jacksonville, Pittsburgh and then check group_desc = 'HERNDONIT' OR 'HERNDON FACILITIES'.

            I am trying with,

            (UPPER(group_desc) LIKE (UPPER(SUBSTR (location_desc, 3,INSTR(SUBSTR(location_desc,3,LENGTH(location_desc)),',')-1)) || ' IT'|| '%')

                             OR  UPPER(group_desc) LIKE (UPPER(SUBSTR (location_desc, 3,INSTR(SUBSTR(location_desc,3,LENGTH(location_desc)),',')-1)) || ' FACILITIES' || '%' ))

            But its not working correctly. Eventhough there is data i am not getting any row return.

             

            Thanks,

            Gayatri.

            • 3. Re: Like is not working with substr...
              1013527

              OK i resolved it. I added a TRIM then it work out.

               

              Thanks for reply.