7 Replies Latest reply: Sep 10, 2013 7:45 AM by chris227 RSS

    Not like operator not working while matching text from two tables

    Costa

      Hello  Everyone,

       

      I want to find the Id from table child where the column name  is not matching with at least first term of column name from parent table.

      I am not getting proper output. can anyone help me.

      Output should be :-->ID 6 & 7

       

       

       

      with child as

      (select 1 id, 'Genentech'  as name from dual union all

      select 2 id, 'Altana Pharma AG'  as name from dual union all

      select 3 id, 'Yamanouchi'  as name from dual union all

      select 4 id, 'Sigma-Tau'  as name from dual union all

      select 5 id, 'Schering-Plough'  as name  from dual union all

      select 6 id, 'Pharma AG'  as name from dual union all

      select 7 id, 'Pfizer'  as name  from dual

      ), parent as

       

      (select 1 id, 'Genentech number'  as names from dual union all

      select 2 id, 'Altana Pharma AG'  as names from dual union all

      select 3 id, 'AG site/Yamanouchi'  as names from dual union all

      select 4 id, 'sigMa Tau'  as names from dual union all

      select 5 id, 'Schering-Plough'  as names  from dual union all

      select 6 id, 'AG'  as names from dual union all

      select 7 id, 'Inc'  as names  from dual

      )

       

       

      select *

      from child a, parent bc

      where a.id=bc.id

      and upper(a.name) not like (bc.names)

        • 1. Re: Not like operator not working while matching text from two tables
          Karthick_Arp

          Like this?

           

          select c.id

               , c.name

               , p.names

            from child c

            join parent p

              on c.id = p.id

          where lower(p.names) not like '%' || lower(c.name) || '%';

           

          ID NAME             NAMES          

          -- ---------------- ------------------

          4 Sigma-Tau        sigMa Tau        

          6 Pharma AG        AG               

          7 Pfizer           Inc             

          • 2. Re: Not like operator not working while matching text from two tables
            Purvesh K


            I am not sure, I understand your requirement properly, however, there are two mistakes in your approach:

            1. Using UPPER function on LHS but not on RHS.

            2. Incorrect way of using LIKE. It has to be appended with %, before and/or after as required.

             

             

            See below::

            with child as

            (select 1 id, 'Genentech'  as name from dual union all

            select 2 id, 'Altana Pharma AG'  as name from dual union all

            select 3 id, 'Yamanouchi'  as name from dual union all

            select 4 id, 'Sigma-Tau'  as name from dual union all

            select 5 id, 'Schering-Plough'  as name  from dual union all

            select 6 id, 'Pharma AG'  as name from dual union all

            select 7 id, 'Pfizer'  as name  from dual

            ), parent as

            (select 1 id, 'Genentech number'  as names from dual union all

            select 2 id, 'Altana Pharma AG'  as names from dual union all

            select 3 id, 'AG site/Yamanouchi'  as names from dual union all

            select 4 id, 'sigMa Tau'  as names from dual union all

            select 5 id, 'Schering-Plough'  as names  from dual union all

            select 6 id, 'AG'  as names from dual union all

            select 7 id, 'Inc'  as names  from dual

            )

            select *

            from child a, parent bc

            where a.id=bc.id

            and upper(a.name) not like upper(bc.names) || '%';

            • 3. Re: Not like operator not working while matching text from two tables
              Costa

              Thank you both for your help.

               

               

              But I am not getting the actual records,my question is if it is  not matching with at least first term of column name from parent table.

              The output I got is :

               

               

               

              1 Genentech 1 Genentech number

              3 Yamanouchi 3 AG site/Yamanouchi

              4 Sigma-Tau 4 sigMa Tau

              6 Pharma AG 6 AG

              7 Pfizer 7 Inc

               

              See for ID 1 and 4, the text is matching. is it possible to skip those record (although hyphen or other spl char there e.g. hyphen for Sigma-Tau)

              • 4. Re: Not like operator not working while matching text from two tables
                Manik

                One way:

                 

                WITH child AS

                        (SELECT 1 id, 'Genentech' AS name FROM DUAL

                         UNION ALL

                         SELECT 2 id, 'Altana Pharma AG' AS name FROM DUAL

                         UNION ALL

                         SELECT 3 id, 'Yamanouchi' AS name FROM DUAL

                         UNION ALL

                         SELECT 4 id, 'Sigma-Tau' AS name FROM DUAL

                         UNION ALL

                         SELECT 5 id, 'Schering-Plough' AS name FROM DUAL

                         UNION ALL

                         SELECT 6 id, 'Pharma AG' AS name FROM DUAL

                         UNION ALL

                         SELECT 7 id, 'Pfizer' AS name FROM DUAL),

                     parent AS

                        (SELECT 1 id, 'Genentech number' AS names FROM DUAL

                         UNION ALL

                         SELECT 2 id, 'Altana Pharma AG' AS names FROM DUAL

                         UNION ALL

                         SELECT 3 id, 'AG site/Yamanouchi' AS names FROM DUAL

                         UNION ALL

                         SELECT 4 id, 'sigMa Tau' AS names FROM DUAL

                         UNION ALL

                         SELECT 5 id, 'Schering-Plough' AS names FROM DUAL

                         UNION ALL

                         SELECT 6 id, 'AG' AS names FROM DUAL

                         UNION ALL

                         SELECT 7 id, 'Inc' AS names FROM DUAL)

                SELECT *

                  FROM child a, parent bc

                WHERE a.id = bc.id

                       AND UPPER (REGEXP_REPLACE (a.name, '[^[:alnum:]]')) NOT LIKE

                              '%' || UPPER (REGEXP_REPLACE (bc.names, '[^[:alnum:]]')) || '%';

                 

                Regexp_replace can be avoided and replaced by translate there if you know for sure what characters you are expecting.

                 

                ID NAME ID_1 NAMES

                1 Genentech 1 Genentech number

                3 Yamanouchi 3 AG site/Yamanouchi

                7 Pfizer 7 Inc


                Cheers,

                Manik.

                • 5. Re: Not like operator not working while matching text from two tables
                  BluShadow

                  You need to clearly define the logic you are expecting as it's not clear.

                   

                  In your original post you say you expect Id's 6 and 7, and then in your post above you say that out of 1,3,4,6,7 you want to skip 1 and 4, which means you are expecting 3, 6 and 7.

                   

                  So just what is the requirement exactly?

                   

                  SQL> ed
                  Wrote file afiedt.buf

                    1  with child as
                    2    (select 1 id, 'Genentech'  as name from dual union all
                    3     select 2 id, 'Altana Pharma AG'  as name from dual union all
                    4     select 3 id, 'Yamanouchi'  as name from dual union all
                    5     select 4 id, 'Sigma-Tau'  as name from dual union all
                    6     select 5 id, 'Schering-Plough'  as name  from dual union all
                    7     select 6 id, 'Pharma AG'  as name from dual union all
                    8     select 7 id, 'Pfizer'  as name  from dual
                    9    )
                  10    , parent as
                  11    (select 1 id, 'Genentech number'  as names from dual union all
                  12     select 2 id, 'Altana Pharma AG'  as names from dual union all
                  13     select 3 id, 'AG site/Yamanouchi'  as names from dual union all
                  14     select 4 id, 'sigMa Tau'  as names from dual union all
                  15     select 5 id, 'Schering-Plough'  as names  from dual union all
                  16     select 6 id, 'AG'  as names from dual union all
                  17     select 7 id, 'Inc'  as names  from dual
                  18    )
                  19  select c.*, p.*
                  20  from child c, parent p
                  21  where c.id=p.id
                  22* and regexp_substr(regexp_replace(upper(c.name),'[^A-Z]', ' '),'[^ ]+') not like regexp_substr(regexp_replace(upper(p.names),'[^A-Z]', ' '),'[^ ]+')
                  SQL> /

                   

                          ID NAME                     ID NAMES
                  ---------- ---------------- ---------- ------------------
                           3 Yamanouchi                3 AG site/Yamanouchi
                           6 Pharma AG                 6 AG
                           7 Pfizer                    7 Inc

                  • 6. Re: Not like operator not working while matching text from two tables
                    940315

                    Hi costa,

                     

                    you can achieve this by adding one more condition at the last of the query. which exclude the row is first name match and you have to use upper or lower function both side in both the condition then you will get result as you aspect.

                     

                    with child as

                    (select 1 id, 'Genentech'  as name from dual union all

                    select 2 id, 'Altana Pharma AG'  as name from dual union all

                    select 3 id, 'Yamanouchi'  as name from dual union all

                    select 4 id, 'Sigma-Tau'  as name from dual union all

                    select 5 id, 'Schering-Plough'  as name  from dual union all

                    select 6 id, 'Pharma AG'  as name from dual union all

                    select 7 id, 'Pfizer'  as name  from dual

                    ), parent as

                     

                    (select 1 id, 'Genentech number'  as names from dual union all

                    select 2 id, 'Altana Pharma AG'  as names from dual union all

                    select 3 id, 'AG site/Yamanouchi'  as names from dual union all

                    select 4 id, 'sigMa Tau'  as names from dual union all

                    select 5 id, 'Schering-Plough'  as names  from dual union all

                    select 6 id, 'AG'  as names from dual union all

                    select 7 id, 'Inc'  as names  from dual

                    )

                     

                     

                    select *

                    from child a, parent bc

                    where a.id=bc.id

                    and upper(a.name) not like upper(bc.names)

                    and REGEXP_SUBSTR(upper(a.name),'[[:alpha:]]+')<>REGEXP_SUBSTR(upper(bc.names),'[[:alpha:]]+')

                    • 7. Re: Not like operator not working while matching text from two tables
                      chris227

                      You may consider the usage of oracle text

                       

                      CREATE INDEX idx_parent_name_cat ON parent (names)
                         INDEXTYPE IS CTXSYS.ctxcat

                       

                      select *
                      from child c
                      where not exists
                      (select 1 from parent p
                      where
                      c.id=p.id
                      and
                      catsearch(p.names,c.name,null) > 0
                      )

                       

                      IDNAME
                      6Pharma AG
                      7Pfizer

                       

                      I must admin that it is not the most easy thing to start using oracle text.

                       

                      If of interest i posted a question on this in the oracel text forum, because i didnt know it off the reel and hadnt enough time to figure it out myself.

                       

                      Message was edited by: chris227 link to follow up