1 2 Previous Next 29 Replies Latest reply: Dec 13, 2012 6:15 AM by 976910 Go to original post RSS
      • 15. Re: count based on soundex function
        976910
        Sorry,
        i was mistake in response to you.

        but i want to know how can i used the update statement not inserting why? because the words already inserted into B table just in want to update the column.
        my best regards to you.

        Edited by: 973907 on Dec 9, 2012 7:46 AM
        • 16. Re: count based on soundex function
          976910
          Many thanks for you.
          i appraciate yor effor.

          But when i add just one word Like Egypt twice in row id no=1
          and also in one time in row id =3.
          the output will be empty for counting.
          like this:

          drop table a;
          create table  a(id number(9), gen_value varchar2(200));
           
          insert into a values(1, 'ACD BCD AAA, AFD BCF , Egypt,BDE AAC AFC,Egypt,BCF AAE');
          insert into a values(2, 'AVF BCD, BBA BBG BCEV, GACD MNF BCV');
          insert into a values(3, 'AFC ABC, BBG HUH ABCE, Egypt,JHU KK MNK');
          
          drop table B ;
          create table B (bid number(9), words varchar2(50), per number(9));
           
          insert into b values (1,'ACD BCD AAA',null);
          insert into b values (1,'BDE AAC AFC',null);         
          insert into b values (1,'AFD BCF',null);    
          insert into b values (1,'EGYPT',null);
          insert into b values (1,'BDE AAC AFC',null);         
          insert into b values (1,'AFD BCF',null);     
          insert into b values (2,'BBA BBG BCEV',null);
          insert into b values (2,'AVF BCD',null);
          insert into b values (3,'BBG HUH ABCE',null);
          insert into b values (3,'JHU KK MNK',null);
          insert into b values (3,'EGYPT',null);
          update B
          set per = (
          with data as (
           select
            id
           ,gen_value||',' gen_value
           from a
          )
          ,r  (id, gen_value, l) as (
           select  
            id
           ,substr(gen_value, instr(gen_value, ',') + 1)
           ,substr(
             trim(substr(gen_value,1, instr(gen_value, ',') - 1))
            ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
           )
          from data
          union all
           select  
            id
           ,substr(gen_value, instr(gen_value, ',') + 1)
           ,substr(
             trim(substr(gen_value,1, instr(gen_value, ',') - 1))
            ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
           )
          from r
          where
          length(gen_value) > 0
          )
          , cnts as ( 
          select 
          -- soundex(l) s
          count(*) cnt
          ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
          --,id
          from r
          group by
           soundex(l), id
          )
           select
           cnts.cnt
          from cnts
          where
          words like '%'||v );
          select * from b;
                 BID WORDS                       PER
          ---------- -------------------- ----------
                   1 ACD BCD AAA                   2
                   1 BDE AAC AFC                   1
                   1 AFD BCF                          1
                   1 EGYPT                                                   -- This count is empty
                   1 BDE AAC AFC                   1
                   1 AFD BCF                          1
                   2 BBA BBG BCEV                  2
                   2 AVF BCD                           1
                   3 BBG HUH ABCE                  2
                   3 JHU KK MNK                      1
                   3 EGYPT                                                      -- this is also empty . Why?
          
          11 rows selected.
          
          SQL>
          when i tried to add soundex for words and V.
          for last part like this
           select
           cnts.cnt
          from cnts
          where
          soundex (words) like '%'||soundex (v) );
          The error msg appear like this
          SQL> /
          with data as (
          *
          ERROR at line 3:
          ORA-01427: single-row subquery returns more than one row
          
          
          SQL>
          the expected results should be like this
                 BID WORDS                       PER
          ---------- -------------------- ----------
                   1 ACD BCD AAA                  2
                   1 BDE AAC AFC                   1
                   1 AFD BCF                          1
                   1 EGYPT                            2
                   1 BDE AAC AFC                   1
                   1 AFD BCF                          1
                   2 BBA BBG BCEV                 2
                   2 AVF BCD                         1
                   3 BBG HUH ABCE                2
                   3 JHU KK MNK                    1
                   3 EGYPT                           1                  
          Note : i just change id in table B to differentiate with id in table A. and column CNT to Per to differentiate in SQL code.

          the results of this is acceptable but the update part is not included:
          with data as (
           select
            id
           , gen_value ||','  gen_value 
           from a
          )
          ,r  (id,  gen_value , l) as (
           select  
            id
           ,substr( gen_value , instr( gen_value , ',') + 1)
           ,substr(
             trim(substr( gen_value ,1, instr( gen_value , ',') - 1))
            ,instr(trim(substr( gen_value ,1, instr( gen_value , ',') - 1)),' ',-1)
           )
          from data
          union all
           select  
            id
           ,substr( gen_value , instr( gen_value , ',') + 1)
           ,substr(
             trim(substr( gen_value ,1, instr( gen_value , ',') - 1))
            ,instr(trim(substr( gen_value ,1, instr( gen_value , ',') - 1)),' ',-1)
           )
          from r
          where
          length( gen_value ) > 0
          )
           select id,
           soundex(l)as l
          ,count(*) cnt
          ,trim(max(l) keep ( dense_rank last order by length(l), l desc nulls first)) v
          from r where l is not null
          group by
           soundex(l),id
           order by 1;
          Edited by: 973907 on Dec 8, 2012 9:07 PM
          • 17. Re: count based on soundex function
            Frank Kulash
            Hi,

            Remeber that string comparisons in Oracle are case-sensitive. 'Egypt' (with some small letters) is not the same as 'EGYPT' (all capital letters.)
            Perhaps you need to change:
            words like '%'||v );
            at the end to
            UPPER (words) like '%' || UPPER (v) );
            Sorry, I'm not at an Oracle 11.2 database now. so I can test it.
            • 18. Re: count based on soundex function
              AlbertoFaenza
              973907 wrote:
              Sorry,
              i was mistake in response to you.

              but i want to know how can i used the update statement not inserting why? because the words already inserted into B table just in want to update the column.
              my best regards to you.

              Edited by: 973907 on Dec 9, 2012 7:46 AM
              Then you can modify my query and use it for MERGE in this way:
              drop table a;
              create table  a(id number(9), gen_value varchar2(200));
               
              insert into a values(1, 'ACD BCD AAA, AFD BCF , Egypt,BDE AAC AFC,Egypt,BCF AAE');
              insert into a values(2, 'AVF BCD, BBA BBG BCEV, GACD MNF BCV');
              insert into a values(3, 'AFC ABC, BBG HUH ABCE, Egypt,JHU KK MNK');
               
              drop table B ;
              create table B (bid number(9), words varchar2(50), per number(9));
               
              insert into b values (1,'ACD BCD AAA',null);
              insert into b values (1,'BDE AAC AFC',null);         
              insert into b values (1,'AFD BCF',null);    
              insert into b values (1,'EGYPT',null);
              insert into b values (1,'BDE AAC AFC',null);         
              insert into b values (1,'AFD BCF',null);     
              insert into b values (2,'BBA BBG BCEV',null);
              insert into b values (2,'AVF BCD',null);
              insert into b values (3,'BBG HUH ABCE',null);
              insert into b values (3,'JHU KK MNK',null);
              insert into b values (3,'EGYPT',null);
              commit;
              
              MERGE INTO b
              USING
              (
                 WITH got_values AS
                 (
                    SELECT id 
                         , REGEXP_SUBSTR(TRIM((column_value).getstringval()),'\w+$') str
                      FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
                 )
                    SELECT b.ROWID rid
                         , COUNT(*) cnt
                      FROM got_values t, b
                     WHERE b.bid=t.id 
                       AND SOUNDEX(str) = SOUNDEX(REGEXP_SUBSTR(words,'\w+$')) 
                     GROUP BY b.rowid, id, SOUNDEX(str)
              ) qry ON (qry.rid=b.rowid)
              WHEN MATCHED THEN
                 UPDATE SET per=qry.cnt;
                 
              SELECT * FROM b;
              
                     BID WORDS                  PER
              ---------- --------------- ----------
                       1 ACD BCD AAA              2
                       1 BDE AAC AFC              1
                       1 AFD BCF                  1
                       1 EGYPT                    2
                       1 BDE AAC AFC              1
                       1 AFD BCF                  1
                       2 BBA BBG BCEV             2
                       2 AVF BCD                  1
                       3 BBG HUH ABCE             2
                       3 JHU KK MNK               1
                       3 EGYPT                    1
              Regards.
              Al

              Edited by: Alberto Faenza on Dec 10, 2012 9:39 AM
              MERGE statement simplified.
              • 19. Re: count based on soundex function
                chris227
                One possibility like frank already showed
                update B
                set per = (
                with data as (
                 select
                  id
                 ,gen_value||',' gen_value
                 from a
                )
                ,r  (id, gen_value, l) as (
                 select  
                  id
                 ,substr(gen_value, instr(gen_value, ',') + 1)
                 ,substr(
                   trim(substr(gen_value,1, instr(gen_value, ',') - 1))
                  ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
                 )
                from data
                union all
                 select  
                  id
                 ,substr(gen_value, instr(gen_value, ',') + 1)
                 ,substr(
                   trim(substr(gen_value,1, instr(gen_value, ',') - 1))
                  ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
                 )
                from r
                where
                length(gen_value) > 0
                )
                , cnts as ( 
                select 
                -- soundex(l) s
                count(*) cnt
                ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
                ,id
                from r
                group by
                 soundex(l), id
                )
                 select
                 cnts.cnt
                from cnts
                where
                b.bid=cnts.id
                and
                upper(b.words) like '%'||upper(cnts.v)
                );
                
                select
                 *
                from b
                
                BID WORDS PER 
                1 EGYPT 2 
                1 BDE AAC AFC 1 
                1 AFD BCF 1 
                1 ACD BCD AAA 2 
                2 AVF BCD 1 
                1 BDE AAC AFC 1 
                1 AFD BCF 1 
                2 BBA BBG BCEV 2 
                3 BBG HUH ABCE 2 
                3 JHU KK MNK 1 
                3 EGYPT 1 
                I added the join on bid=id, wasnt not sure if this was wanted before.
                May be it could be seen, that i tried not to use regexp-functions so far.
                I dont know your real requirements, but perhaps it would be advisable to rethink your data model and to consider the usage of oracle text.
                • 20. Re: count based on soundex function
                  976910
                  many thanks for you and to all here in this nice forum.
                  • 21. Re: count based on soundex function
                    976910
                    I tried to use your code. But i face little bit incorrect in results as below output of the SQL query that is
                    not count the last part of sentence for id =2 and put the best value.
                    For id=2 the last part is Majid because in row id no 2 there are two sentences end with Majid.

                    drop table a;
                    CREATE TABLE  a(id NUMBER(9), gen_value VARCHAR2(200));
                     
                    INSERT INTO a VALUES(1, 'ACD BCD AAA, AFD BCF, 88,Egypt,BDE AAC AFC,BCF AAE,Egypt,Egpt');
                    INSERT INTO a VALUES(2, 'AVF BCD,omar Ahmed Majid ,BBA BBG BCEV,ALI Majid, GACD MNF BCV');
                    INSERT INTO a VALUES(3, 'AFC ABC, BBG HUH ABCE, JHU KK MNK');
                    COMMIT;
                     drop table b;
                    CREATE TABLE  b(id NUMBER(9), Gen_Value VARCHAR2(200), cnt NUMBER(9));
                     
                    INSERT INTO b (id, gen_value, cnt)
                    WITH got_values AS
                    (
                       SELECT id, TRIM((column_value).getstringval()) strorig
                            , REGEXP_SUBSTR((column_value).getstringval(),'\w+$') str
                         FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
                    )
                    , got_sdx_cnt AS
                    (
                       SELECT id, strorig
                            , COUNT(*) OVER (PARTITION BY id, SOUNDEX(str)) cnt
                            , ROW_NUMBER() OVER (PARTITION BY id, SOUNDEX(str) ORDER BY LENGTH(str) DESC, ROWNUM) rn
                         FROM got_values
                    )
                    SELECT id, strorig, cnt 
                      FROM got_sdx_cnt
                     WHERE rn=1;
                     
                    SQL> SELECT * FROM b;
                    
                            ID GEN_VALUE                   CNT
                    ---------- -------------------- ----------
                             1 ACD BCD AAA                   2
                             1 BDE AAC AFC                    1
                             1 AFD BCF                           1
                             1 Egypt                              3
                             1 88                                   1
                             2 BBA BBG BCEV                  2
                             2 AVF BCD                          1
                             2 ALI Majid                          1
                             2 omar Ahmed Majid             1
                             3 BBG HUH ABCE                  2
                             3 JHU KK MNK                      1
                    
                    11 rows selected.
                    
                    SQL>
                    but the expected results are
                    SQL> SELECT * FROM b;
                    
                            ID GEN_VALUE                   CNT
                    ---------- -------------------- ----------
                             1 ACD BCD AAA                  2
                             1 BDE AAC AFC                   1
                             1 AFD BCF                          1
                             1 Egypt                             3
                             1 88                                  1
                             2 BBA BBG BCEV                 2
                             2 AVF BCD                         1
                             2 omar Ahmed Majid            2
                             3 BBG HUH ABCE                2
                             3 JHU KK MNK                    1
                    
                    11 rows selected.
                    
                    SQL>
                    only the soundex for last part and put best value (that has long length)
                    • 22. Re: count based on soundex function
                      AlbertoFaenza
                      Hi,

                      this is due to the space after "omar Ahmed Majid" and before the comma.
                      INSERT INTO a VALUES(2, 'AVF BCD,omar Ahmed Majid ,BBA BBG BCEV,ALI Majid, GACD MNF BCV');
                      To fix the issue simply use TRIM before the REGEXP_SUBSTR in this way:
                      INSERT INTO b (id, gen_value, cnt)
                      WITH got_values AS
                      (
                         SELECT id, TRIM((column_value).getstringval()) strorig
                              , REGEXP_SUBSTR(TRIM((column_value).getstringval()),'\w+$') str -- This row is changed
                           FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
                      )
                      , got_sdx_cnt AS
                      (
                         SELECT id, strorig
                              , COUNT(*) OVER (PARTITION BY id, SOUNDEX(str)) cnt
                              , ROW_NUMBER() OVER (PARTITION BY id, SOUNDEX(str) ORDER BY LENGTH(str) DESC, ROWNUM) rn
                           FROM got_values
                      )
                      SELECT id, strorig, cnt 
                        FROM got_sdx_cnt
                       WHERE rn=1;
                      
                      Result:
                              ID GEN_VALUE                   CNT
                      ---------- -------------------- ----------
                               1 ACD BCD AAA                   2
                               1 BDE AAC AFC                   1
                               1 AFD BCF                       1
                               1 Egypt                         3
                               1 88                            1
                               2 BBA BBG BCEV                  2
                               2 AVF BCD                       1
                               2 omar Ahmed Majid              2
                               3 BBG HUH ABCE                  2
                               3 JHU KK MNK                    1
                      Regards.
                      Al

                      Edited by: Alberto Faenza on Dec 10, 2012 9:16 PM
                      Missing right parenthesis in query
                      • 23. Re: count based on soundex function
                        976910
                        Thanks a lot.
                        • 24. Re: count based on soundex function
                          976910
                          Now i face another incorrect results for date because count all dates and numbers as one value.
                          see below.
                          drop table a;
                          CREATE TABLE  a(id NUMBER(9), gen_value VARCHAR2(200));
                           
                          INSERT INTO a VALUES(1, 'ACD BCD AAA, AFD BCF, 88,Egypt,BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010');
                          INSERT INTO a VALUES(2, 'AVF BCD,omar Ahmed Majid,BBA BBG BCEV,ALI Majid, GACD MNF BCV');
                          INSERT INTO a VALUES(3, 'AFC ABC, BBG HUH ABCE, JHU KK MNK');
                          COMMIT;
                           drop table b;
                          CREATE TABLE  b(id NUMBER(9), Gen_Value VARCHAR2(200), cnt NUMBER(9));
                           
                          i used this
                          INSERT INTO b (id, gen_value, cnt)
                          WITH got_values AS
                          (
                             SELECT id, TRIM((column_value).getstringval()) strorig
                                  ,TRIM( REGEXP_SUBSTR(column_value).getstringval()),'\w+$') str 
                               FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
                          )
                          , got_sdx_cnt AS
                          (
                             SELECT id, strorig
                                  , COUNT(*) OVER (PARTITION BY id, SOUNDEX(str)) cnt
                                  , ROW_NUMBER() OVER (PARTITION BY id, SOUNDEX(str) ORDER BY LENGTH(str) DESC, ROWNUM) rn
                               FROM got_values
                          )
                          SELECT id, strorig, cnt 
                            FROM got_sdx_cnt
                           WHERE rn=1;
                          but the results is not correct
                          SQL> /
                                   1 ACD BCD AAA                   2
                                   1 BDE AAC AFC                   1
                                   1 AFD BCF                          1
                                   1 Egypt                             3
                                   1 1-1-2010                         4
                                   2 BBA BBG BCEV                 2
                                   2 AVF BCD                         1
                                   2 omar Ahmed Majid            2
                                   3 BBG HUH ABCE                2
                                   3 JHU KK MNK                    1
                          
                          10 rows selected.
                          
                          SQL>
                          the expected results like this
                          SQL> /
                                   1 ACD BCD AAA                  2
                                   1 BDE AAC AFC                   1
                                   1 AFD BCF                          1
                                   1 Egypt                             3
                                   1 1-1-2010                         2
                                   1  88                                 1
                                   1  20-11-2010                    1
                                   2 BBA BBG BCEV                 2
                                   2 AVF BCD                         1
                                   2 omar Ahmed Majid            2
                                   3 BBG HUH ABCE                2
                                   3 JHU KK MNK                    1
                          
                          
                          
                          SQL>
                          Edited by: 973907 on Dec 10, 2012 10:25 AM

                          Edited by: 973907 on Dec 10, 2012 10:26 AM
                          • 25. Re: count based on soundex function
                            976910
                            i update your SQL and i got correct results then i will add INSERT statement but i face problem as shown below that show the soundex value not string.So, i want to substitute soundex value by string(words).
                            select id,NVL(soundex(str),strorig) as str,COUNT(*)  cnt from (SELECT id, TRIM((column_value).getstringval()) strorig
                                    , trim(REGEXP_SUBSTR((column_value).getstringval(),'\w+$')) str
                                 FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"'))) group by NVL(soundex(str),strorig),id; 
                            results
                            SQL> /
                                     1 20-11-2010               1
                                     2 B230                     1
                                     2 B210                     2
                                     1 A000                     2
                                     3 A120                     2
                                     1 B210                     1
                                     1 88                       1
                                     1 A120                     1
                                     1 E213                     3
                                     2 M230                     2
                                     3 M200                     1
                                     1 1-1-2010                 2
                            
                            12 rows selected.
                            
                            SQL>
                            • 26. Re: count based on soundex function
                              AlbertoFaenza
                              Hi,

                              not knowing exactly all the cases that are coming out I changed the following:

                              1) The pattern for regular expression is: '\S+$'. In this way is considering any nonwhitespace character rather than \w that is considering alphanumeric or underscore(_)
                              2) I have changed the partition by in the analytic functions so when the soundex function is NULL it will consider the original string for partition.

                              I don't know if this is ok for all of your cases.

                              Here below the test I've done:
                              drop table a;
                              CREATE TABLE  a(id NUMBER(9), gen_value VARCHAR2(200));
                               
                              INSERT INTO a VALUES(1, 'ACD BCD AAA, AFD BCF, 88,Egypt,BDE AAC AFC,1-1-2010,20-11-2010,BCF AAE,Egypt,Egpt,1-1-2010');
                              INSERT INTO a VALUES(2, 'AVF BCD,omar Ahmed Majid,BBA BBG BCEV,ALI Majid, GACD MNF BCV');
                              INSERT INTO a VALUES(3, 'AFC ABC, BBG HUH ABCE, JHU KK MNK');
                              COMMIT;
                               drop table b;
                              CREATE TABLE  b(id NUMBER(9), Gen_Value VARCHAR2(200), cnt NUMBER(9));
                              
                              INSERT INTO b (id, gen_value, cnt)
                              WITH got_values AS
                              (
                                 SELECT id, TRIM((column_value).getstringval()) strorig
                                      , REGEXP_SUBSTR(TRIM((column_value).getstringval()),'\S+$') str
                                   FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
                              )
                              , got_sdx_cnt AS
                              (
                                 SELECT id, strorig
                                      , COUNT(*) OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig)) cnt
                                      , ROW_NUMBER() OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig) 
                                                               ORDER BY LENGTH(str) DESC, ROWNUM) rn
                                   FROM got_values
                              )
                              SELECT id, strorig, cnt 
                                FROM got_sdx_cnt
                               WHERE rn=1;
                               
                              SET LINE 500
                              COL GEN_VALUE FORMAT A20
                              SELECT * FROM b;
                              
                                      ID GEN_VALUE                   CNT
                              ---------- -------------------- ----------
                                       1 1-1-2010                      2
                                       1 20-11-2010                    1
                                       1 88                            1
                                       1 ACD BCD AAA                   2
                                       1 BDE AAC AFC                   1
                                       1 AFD BCF                       1
                                       1 Egypt                         3
                                       2 BBA BBG BCEV                  2
                                       2 AVF BCD                       1
                                       2 omar Ahmed Majid              2
                                       3 BBG HUH ABCE                  2
                                       3 JHU KK MNK                    1
                              Regards.
                              Al
                              • 27. Re: count based on soundex function
                                976910
                                Many thanks Alberto Faenza ,

                                I have now maybe 2000 rows in my database but when
                                execute this
                                INSERT INTO b (id, gen_value, cnt)
                                WITH got_values AS
                                (
                                   SELECT id, TRIM((column_value).getstringval()) strorig
                                        , REGEXP_SUBSTR(TRIM((column_value).getstringval()),'\S+$') str
                                     FROM a, xmltable(('"'||REPLACE(gen_value,',','","')||'"')) 
                                )
                                , got_sdx_cnt AS
                                (
                                   SELECT id, strorig
                                        , COUNT(*) OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig)) cnt
                                        , ROW_NUMBER() OVER (PARTITION BY id, NVL(SOUNDEX(str), strorig) 
                                                                 ORDER BY LENGTH(str) DESC, ROWNUM) rn
                                     FROM got_values
                                )
                                SELECT id, strorig, cnt 
                                  FROM got_sdx_cnt
                                 WHERE rn=1;
                                I face this error :
                                INSERT INTO b (id, gen_value, cnt)
                                *
                                ERROR at line 1:
                                ORA-01489: result of string concatenation is too long
                                • 28. Re: count based on soundex function
                                  chris227
                                  I guess you was given a solution already which doesnt lead to this problem.
                                  Dont think people dont use some kind of functions just beacuse a lack of knowledge (which is sometimes helpful tho')
                                  • 29. Re: count based on soundex function
                                    976910
                                    Many thanks Chris277,

                                    Yes you are right , But your solution using the UPDATE statement. Later i want the insert statement As well as
                                    count the number, date.

                                    i will use your solution.
                                    But please help me how can i add the insert statement and count the numbers and date.


                                    Many thanks for you
                                    update b
                                    set cnt = (
                                    with data as (
                                     select
                                      id
                                     ,gen_value||',' gen_value
                                     from a
                                    )
                                    ,r  (id, gen_value, l) as (
                                     select  
                                      id
                                     ,substr(gen_value, instr(gen_value, ',') + 1)
                                     ,substr(
                                       trim(substr(gen_value,1, instr(gen_value, ',') - 1))
                                      ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
                                     )
                                    from data
                                    union all
                                     select  
                                      id
                                     ,substr(gen_value, instr(gen_value, ',') + 1)
                                     ,substr(
                                       trim(substr(gen_value,1, instr(gen_value, ',') - 1))
                                      ,instr(trim(substr(gen_value,1, instr(gen_value, ',') - 1)),' ',-1)
                                     )
                                    from r
                                    where
                                    length(gen_value) > 0
                                    )
                                    , cnts as ( 
                                    select 
                                    -- soundex(l) s
                                    count(*) cnt
                                    ,max(l) keep ( dense_rank last order by length(l), l desc nulls first) v
                                    --,id
                                    from r
                                    group by
                                     soundex(l), id
                                    )
                                     
                                    select
                                     cnts.cnt
                                    from cnts
                                    where
                                    words like '% '||v
                                    )
                                     
                                    7 row(s) updated.
                                     
                                    select * from b
                                     
                                    ID WORDS CNT 
                                    1 ACD BCD AAA 2 
                                    1 BDE AAC AFC 1 
                                    3 JHU KK MNK 1 
                                    2 AVF BCD 1 
                                    2 BBA BBG BCEV 2 
                                    3 BBG HUH ABCE 2 
                                    1 AFD BCF 1
                                    1 2 Previous Next