Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    9

    Unanswered: Need to create a unique word listing

    Sorry its been a long while sence ive written SQL code. Man am I rusty.


    What I need to do is query a description field and pull out each unique word. I need to build a dictionary of unique words for an IVR system.

    Thanks

  2. #2
    Join Date
    Oct 2004
    Posts
    145

    Need to create a unique word listing

    Are you looking for something simple as

    "select distinct "

  3. #3
    Join Date
    Oct 2004
    Posts
    9
    That would give me each discription but what I need is each unique word within ALL descriptions

    Select unique would give me

    10 kt gold ruby ring
    18 kt gold diamond ring

    what I need is

    10
    18
    kt
    gold
    ruby
    diamond
    ring

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    here's a start.
    PHP Code:
    sys@q9dmds3select 'test me please test I me ' from dual;

    'TESTMEPLEASETESTIME'
    -------------------------
    test me please test I me

    sys
    @q9dmds3select replace('test me please test I me ',' ',chr(10))
      
    2  from dual
      3  
    ;

    REPLACE('TESTMEPLEASETEST
    -------------------------
    test
    me
    please
    test
    I
    me 
    throw them into a temp_table then select distinct.

    hrmmm ... this might not work since oracle still thinks this is one row ...
    HOWEVER! you could spool that to a file, sqlload it to a table where the
    column is distinct! all the duplicates would error out and you have your
    distinct list!
    Last edited by The_Duck; 10-25-04 at 12:28.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you get the PARSE package here, then you can do this:
    Code:
    SQL> declare
      2    v_tab parse.varchar2_table;
      3    v_string varchar2(100) := 'the cat sat on the mat';
      4    v_num integer;
      5  begin
      6    parse.delimstring_to_table(v_string,v_tab,v_num,' ');
      7    for i in 1..v_num loop
      8      dbms_output.put_line(v_tab(i));
      9    end loop;
     10  end;
     11  /
    the
    cat
    sat
    on
    the
    mat
    
    PL/SQL procedure successfully completed.

  6. #6
    Join Date
    Oct 2004
    Posts
    9
    Thanks for the help!

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Assuming you are on 9i or above, you can accomplish this using a Pipelined function, like this:
    Code:
    SQL@9iR2> create table t (wordlist varchar2(4000));
    
    Table created.
    
    SQL@9iR2> insert into t values ('10 kt gold ruby ring');
    
    1 row created.
    
    SQL@9iR2> insert into t values ('18 kt gold diamond ring');
    
    1 row created.
    
    SQL@9iR2> commit;
    
    Commit complete.
    
    SQL@9iR2> create type scalartype as object (word varchar2(4000));
      2  /
    
    Type created.
    
    SQL@9iR2> create type tabletype is table of scalartype;
      2  /
    
    Type created.
    
    SQL@9iR2> create or replace function mypipef(args in varchar2) return tabletype pipelined
      2  is
      3     type wordlist is table of varchar2(4000) index by varchar2(4000);
      4     recword       wordlist;
      5     word          varchar2(4000);
      6     ele           varchar2(4000);
      7     elelist       dbms_sql.varchar2_table;
      8  begin
      9     execute immediate args bulk collect into elelist;
     10     for x in 1..elelist.count
     11     loop
     12         for i in 1..length(elelist(x))
     13         loop
     14             if substr(elelist(x),i,1) = ' '
     15             then
     16                  recword(word) := word;
     17                  word := '';
     18             else
     19                  word := word || substr(elelist(x),i,1);
     20             end if;
     21         end loop;
     22         if length(word) > 0
     23         then
     24            recword(word) := word;
     25         end if;
     26         word := '';
     27     end loop;
     28     ele := recword.first;
     29     while ele is not null
     30     loop
     31             pipe row ( scalartype(recword(ele)) );
     32             ele := recword.next(ele);
     33     end loop;
     34     return;
     35  end;
     36  /
    
    Function created.
    
    SQL@9iR2> select * from table( mypipef('select wordlist from t') );
    
    WORD
    --------------------------------------------------------------------------------
    10
    18
    diamond
    gold
    kt
    ring
    ruby
    
    7 rows selected.

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    That still will not give you a unique list of words.

    As you get each word, you should put it into a hash map with the word as the "key". This will automatically erase duplicates.

    Then print the keys of the hashmap to get the unique values.

    You can use dbms_utility.get_hash_value to get a value for the word.

    I'll try and post some code later.

    Ravi

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    After you've parsed the data, here's some sample code:

    (Please note that I use my own version of the parse function.
    Use your own.
    Also, I have the PL/SQL array tabtype defined in my util package.
    So this code will not work for you as is.)

    Code:
     
    DECLARE 
     
    unique_words util.tabtype;
    words util.tabtype;
     
    current_row integer;
     
    Cursor cur_descr is select descr from unique_descr;
    BEGIN
     
    For rec in cur_descr
    Loop
    	 words := util.string_to_table(rec.descr, ' '); -- this is where the parse algorithm goes in.
    	 For i in 1 .. words.count
    	 Loop
    		 unique_words(dbms_utility.get_hash_value(words(i), 1000, 1000))
    		 := words(i);
    	 End Loop;
    End Loop;
     
    current_row := -1;
    for i in 1..unique_words.count
    loop
    	 current_row := unique_words.next(current_row);
    	 dbms_output.put_line(unique_words(current_row));
    end loop;
    END;
    /
    Hope that helps.

    Ravi

  10. #10
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Re message posted at 13:34 (message #8)

    My earlier message claiming it will not work referred to Andrew's message (#5) and not to JMartinez's message. That, of course, works.

    And is better because it uses the index by varchar2 feature, thus avoiding the use of hash maps.

    Ravi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •