Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    9

    Unanswered: Can you split string according to a list of keywords?

    Hi I want to change the format of a string
    e.g.
    Event
    ---------------------------------------------------
    Peter Parker has a date event on 23/7/2012
    Mary jane watson did not attend event on 23/7/2012

    I have to change the surnames to upper cases but leave the rest intact.
    The current format follows "givenname surname verb otherstuff"
    Because the givenname varies (may be one, two or three words), I reckon it is better to look for "verbs" and so the first word in front of it is the surname.

    My question is can I use a list of verbs to scan for the surname?
    Say I have a table column that stores all the possibile verbs (at least those may appear in the Event column), can you finish the SQL for me?
    update ACTIONS
    set Event = translate(?????, 'abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')

    Thank you!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's one option:
    Code:
    SQL> with
      2  test as
      3    (select 'Peter Parker has a date event on 23/7/2012' col from dual union
      4     select 'Mary jane watson did not attend event on 23/7/2012' from dual
      5    ),
      6  verbs as
      7    (select 'has' verb from dual union
      8     select 'did' from dual
      9    ),
     10  full_name as
     11    (select
     12       trim(substr(t.col, 1, instr(t.col, v.verb) - 1)) fname
     13     from test t,
     14          verbs v
     15    )
     16  select
     17    fname,
     18    upper(substr(fname, instr(fname, ' ', -1) + 1, length(fname))) surname
     19  from full_name
     20  where fname is not null;
    
    FNAME                          SURNAME
    ------------------------------ ----------
    Mary jane watson               WATSON
    Peter Parker                   PARKER
    
    SQL>
    Note that "full_name" table results might be time consuming if both tables (verbs and the events) are large as it performs Cartesian product.

  3. #3
    Join Date
    Jul 2012
    Posts
    9
    Quote Originally Posted by Littlefoot View Post
    Here's one option:
    Note that "full_name" table results might be time consuming if both tables (verbs and the events) are large as it performs Cartesian product.
    Thank you, this is very clever.
    And it may be just one more step, but I still cannot figure out how do I update just the surname to upper using your select statement?
    It would save my day if you can include also the update sql statement

    Also, can I ask for more, say when none of the "verbs" are found, then I still get the whole full string
    e.g.
    Event
    -------------------------------------
    Peter Parker has a date event on 23/7/2012
    Peter Parker, date event on 23/7/2012

    I get "Parker" and "Peter Parker, date event on 23/7/2012"

    Cheers
    Last edited by NewHere; 07-25-12 at 00:02.

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Here is a link regarding how to separate words:
    Ask Tom "Separating comma separated words "

    Look at simple example :
    Code:
    CREATE TABLE test AS
    SELECT 'Peter Parker has a date event on 23/7/2012' event from dual union
    SELECT 'Mary jane watson did not attend event on 23/7/2012' from dual union
    SELECT 'Adam John Peterson was the tallest boy in the class' from dual union
    SELECT 'John Watson' from dual
    ;
    
    CREATE TABLE verbs AS
    SELECT 'HAS' verb from dual union
    SELECT 'DID' from dual ;
    The query that splits strings into words may look like this:
    Code:
    SELECT event, 
           substr( event || ' ' , 1, instr( event, ' ' ) - 1 ) first_word,  
           substr( event || '  ', instr( event || ' ', ' ' ) + 1, instr( event || '  ', ' ', 1, 2 ) -  instr(event|| ' ', ' ' ) - 1 ) second_word,
           substr( event || '   ', instr( event || '  ', ' ', 1, 2 ) + 1, instr( event || '   ', ' ', 1, 3 ) -  instr(event|| '  ', ' ', 1, 2 ) - 1 ) third_word,
           substr( event || '    ', instr( event || '   ', ' ', 1, 3 ) + 1, instr( event || '    ', ' ', 1, 4 ) -  instr(event|| '   ', ' ', 1, 3 ) - 1 ) fourth_word,
           substr( event || '     ', instr( event || '    ', ' ', 1, 4 ) + 1, length( event ) -  instr(event|| '    ', ' ', 1, 4 )  ) the_rest
    FROM test
    ;
    
    EVENT                                               FIRST_WORD                                           SECOND_WORD                                           THIRD_WORD                                             FOURTH_WORD                                             THE_REST                                               
    --------------------------------------------------- ---------------------------------------------------- ----------------------------------------------------- ------------------------------------------------------ ------------------------------------------------------- --------------------------------------------------------
    Adam John Peterson was the tallest boy in the class Adam                                                 John                                                  Peterson                                               was                                                     the tallest boy in the class                             
    John Watson                                         John                                                 Watson                                                                                                                                                                                                                        
    Mary jane watson did not attend event on 23/7/2012  Mary                                                 jane                                                  watson                                                 did                                                     not attend event on 23/7/2012                            
    Peter Parker has a date event on 23/7/2012          Peter                                                Parker                                                has                                                    a                                                       date event on 23/7/2012
    If we have splitting query, doing an update is very simple:
    Code:
    UPDATE (
        SELECT event, 
               substr( event || ' ' , 1, instr( event, ' ' ) - 1 ) first_word,  
               substr( event || '  ', instr( event || ' ', ' ' ) + 1, instr( event || '  ', ' ', 1, 2 ) -  instr(event|| ' ', ' ' ) - 1 ) second_word,
               substr( event || '   ', instr( event || '  ', ' ', 1, 2 ) + 1, instr( event || '   ', ' ', 1, 3 ) -  instr(event|| '  ', ' ', 1, 2 ) - 1 ) third_word,
               substr( event || '    ', instr( event || '   ', ' ', 1, 3 ) + 1, instr( event || '    ', ' ', 1, 4 ) -  instr(event|| '   ', ' ', 1, 3 ) - 1 ) fourth_word,
               substr( event || '     ', instr( event || '    ', ' ', 1, 4 ) + 1, length( event ) -  instr(event|| '    ', ' ', 1, 4 )  ) the_rest
        FROM test
    )
    SET event = first_word || ' ' || 
        CASE
           WHEN exists( SELECT 1 FROM verbs WHERE verb = upper( third_word ) )  THEN upper( second_word ) || ' ' || third_word 
           WHEN exists( SELECT 1 FROM verbs WHERE verb = upper( fourth_word ) ) THEN second_word || ' ' || upper( third_word )
           ELSE second_word || ' ' || third_word
        END || ' ' || fourth_word || ' ' || the_rest
        ;
    Notice that our 'verbs' table contains only uppercase words.
    Here is a result of the update:
    Code:
    select * from test;
    
    EVENT                                             
    ---------------------------------------------------
    Adam John Peterson was the tallest boy in the class 
    John Watson                                         
    Mary jane WATSON did not attend event on 23/7/2012  
    Peter PARKER has a date event on 23/7/2012

Posting Permissions

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