10 Replies Latest reply: Nov 19, 2012 11:43 AM by Ayham RSS

    Update words based on soundex...

    Ayham
      Hi All,

      I have the following table and data , So i want to update this table(word column) with words that has same soundex with
      one word of them, the word must be that has the long length if the two words has same length So, update by anyone of them.

      drop table t1;
      create table t1 (id number(10) ,word varchar2(100));
      
      insert into t1 values(1,'Londn');
      insert into t1 values(2,'Egypt');
      insert into t1 values(3,'London');
      insert into t1 values(4,'Gorgey');
      insert into t1 values(5,'Michal');
      insert into t1 values(6,'Michel');
      insert into t1 values(7,'London');
      insert into t1 values(8,'Egpt');
      insert into t1 values(9,'Londan');
      insert into t1 values(10,'Gorgy');
      insert into t1 values(11,'Gorge');
      the results must be as following
      id    word
      1     London
      2     Egypt
      3     London
      4     Gorey
      5     Michal
      6     Michal
      7     London
      8     Egypt
      9     London
      10   Gorey
      11   Gorey
      Note: this is the results of soundex
      SQL> select soundex(word) from t1;
      
      SOUN
      ----
      L535
      E213
      L535
      G620
      M240
      M240
      L535
      E213
      L535
      G620
      G620
      
      11 rows selected.
      Thanks in advance.
      Ayham.
        • 1. Re: Update words based on soundex...
          Dom Brooks
          Here's one option:
          SQL> select id, word, soundex(word) from t1;
          
                  ID WORD                                SOUN
          ---------- ----------------------------------- ----
                   1 Londn                               L535
                   2 Egypt                               E213
                   3 London                              L535
                   4 Gorgey                              G620
                   5 Michal                              M240
                   6 Michel                              M240
                   7 London                              L535
                   8 Egpt                                E213
                   9 Londan                              L535
                  10 Gorgy                               G620
                  11 Gorge                               G620
          
          11 rows selected.
          
          SQL> merge into t1
            2  using (select id, word, soundex(word), max(word) keep (dense_rank first order by length(word) desc)
            3                                                 over (partition by soundex(word)) mx
            4         from   t1) x
            5  on    (x.id = t1.id)
            6  when matched then
            7    update
            8    set    word = mx
            9    where  t1.word != x.mx;
          
          6 rows merged.
          
          SQL>select id, word, soundex(word) from t1;
          
                  ID WORD                                SOUN
          ---------- ----------------------------------- ----
                   1 London                              L535
                   2 Egypt                               E213
                   3 London                              L535
                   4 Gorgey                              G620
                   5 Michel                              M240
                   6 Michel                              M240
                   7 London                              L535
                   8 Egypt                               E213
                   9 London                              L535
                  10 Gorgey                              G620
                  11 Gorgey                              G620
          
          11 rows selected.
          
          SQL>
          • 2. Best in Group
            Frank Kulash
            Hi,
            Ayham wrote:
            Hi All,

            I have the following table and data , So i want to update this table(word column) with words that has same soundex with
            one word of them, the word must be that has the long length if the two words has same length So, update by anyone of them.

            drop table t1;
            create table t1 (id number(10) ,word varchar2(100));
            
            insert into t1 values(1,'Londn');
            insert into t1 values(2,'Egypt');
            insert into t1 values(3,'London');
            insert into t1 values(4,'Gorgey');
            insert into t1 values(5,'Michal');
            insert into t1 values(6,'Michel');
            insert into t1 values(7,'London');
            insert into t1 values(8,'Egpt');
            insert into t1 values(9,'Londan');
            insert into t1 values(10,'Gorgy');
            insert into t1 values(11,'Gorge');
            the results must be as following
            id    word
            1     London
            2     Egypt
            3     London
            4     Gorey
            5     Michal
            6     Michal
            7     London
            8     Egypt
            9     London
            10   Gorey
            11   Gorey
            'Gorey' doesn';t appear in the original data. I assume that you mean 'Gor<b>g</b>ey' instead.

            Here's one way:
            MERGE INTO  t1     dst
            USING   (
                     SELECT  id
                     ,         FIRST_VALUE (word) OVER ( PARTITION BY  SOUNDEX (word)
                                                          ORDER BY         LENGTH (word)   DESC
                                           ,                 word            DESC
                                        )      AS best_word
                     FROM    t1
                 )        src
            ON     (src.id  = dst.id)
            WHEN MATCHED THEN UPDATE
            SET     dst.word   = src.best_word
            WHERE     dst.word  != src.best_word
            ;
            This is a sort of Top-N Query , where rows with the same value of SOUNDEX (word) form a group (or, as it is called in analytic functions, a Partition ), and you want every rows in that group to have the "best" word in that group. Here "best" is defined as the longest, and, in case of a tie, the last in alphabetic order. For example, 'Londn', 'London' and 'Londan' all belong to the same group, because SOUNDEX (word) = 'L535' in all 3 cases. If we said that hte best word in that group was the longest, then both 'Londan' and 'London' would have an equal claim to being the best, because they are both 6 characters long. To break that tie, I chose the last one in alphbetic order, so 'London' is better than 'Londan' since 'o' comes after 'a'.
            • 3. Re: Update words based on soundex...
              Ayham
              Many thanks for you . But
              SQL>  merge into t1
                2      using (select id, word, soundex(word), max(word) keep (dense_rank first
               order by length(word) desc)
                3     over (partition by soundex(word)) mx
                4             from   t1) x
                5      on    (x.id = t1.id)
                6      when matched then
                7        update
                8        set    word = mx
                9     where  t1.word x.mx;
                 where  t1.word x.mx
                                *
              ERROR at line 9:
              ORA-00920: invalid relational operator
              SQL>

              Edited by: Ayham on Nov 19, 2012 9:17 AM
              • 4. Re: Update words based on soundex...
                Ayham
                Thanks Frank.
                • 5. Re: Update words based on soundex...
                  ranit B
                  Ayham wrote:
                  Many thanks for you . But
                  SQL>  merge into t1
                  2      using (select id, word, soundex(word), max(word) keep (dense_rank first
                  order by length(word) desc)
                  3     over (partition by soundex(word)) mx
                  4             from   t1) x
                  5      on    (x.id = t1.id)
                  6      when matched then
                  7        update
                  8        set    word = mx
                  9     where  t1.word x.mx;
                  where  t1.word x.mx
                  *
                  ERROR at line 9:
                  ORA-00920: invalid relational operator
                  SQL>

                  Edited by: Ayham on Nov 19, 2012 9:17 AM
                  Dom would have used the 'NOT EQUAL TO' operator as '&lt; &gt;' .
                  This would not have happend if '!=' is used.

                  Actually this is an exisitng problem in OTN.
                  • 6. Re: Update words based on soundex...
                    Ayham
                    the question solved but if i have two words with same id like this
                    drop table t1;
                    create table t1 (id number(10) ,word varchar2(100));
                     
                    *insert into t1 values(1,'Londn');*
                    insert into t1 values(2,'Egypt');
                    insert into t1 values(3,'London');
                    insert into t1 values(4,'Gorgey');
                    insert into t1 values(5,'Michal');
                    insert into t1 values(6,'Michel');
                    insert into t1 values(7,'London');
                    insert into t1 values(8,'Egpt');
                    insert into t1 values(9,'Londan');
                    insert into t1 values(10,'Gorgy');
                    insert into t1 values(11,'Gorge');
                    *insert into t1 values(1,'Londaan');*
                    ID 1 has two words.

                    Edited by: Ayham on Nov 19, 2012 9:24 AM
                    • 7. Re: Update words based on soundex...
                      Ayham
                      Yes , i understand that , After Mr.Frank answer.

                      Thanks for all. But now if i have two words under same id.
                      • 8. Re: Update words based on soundex...
                        Frank Kulash
                        Hi,

                        I assumed that id was unique. (Sorry, I should have said that in my original message.)
                        If id is not unique, then use some other expression (or a list of expressions) that is unique, and that doesn't include the column being changed.

                        ROWID is unique in any table, so you can just change id to ROWID:
                        MERGE INTO  t1     dst
                        USING   (
                                 SELECT  ROWID
                                 ,         FIRST_VALUE (word) OVER ( PARTITION BY  SOUNDEX (word)
                                                                      ORDER BY         LENGTH (word)   DESC
                                                       ,                 word            DESC
                                                     )      AS best_word
                                 FROM    t1
                             )        src
                        ON     (src.ROWID  = dst.ROWID)
                        WHEN MATCHED THEN UPDATE
                        SET     dst.word   = src.best_word
                        WHERE     dst.word  != src.best_word
                        ;
                        So if we add this row:
                        insert into t1 values(1,'Londaan');
                        to the original table, then the results are:
                        `       ID WORD
                        ---------- ----------
                                 1 Londaan
                                 1 Londaan
                                 2 Egypt
                                 3 Londaan
                                 4 Gorgey
                                 5 Michel
                                 6 Michel
                                 7 Londaan
                                 8 Egypt
                                 9 Londaan
                                10 Gorgey
                                11 Gorgey
                        • 9. Re: Update words based on soundex...
                          Ayham
                          thanks
                          • 10. Re: Update words based on soundex...
                            Ayham
                            thanks