DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: split column update

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    split column update

    Hi Friends,

    I have a table emp
    (fullname varchar2(90),
    firstname varchar2(30),
    middlename varhar2(30),
    lastname varchar2(30))

    Only the fullname has value, e.g. "MARTIN SCOTT SMITH"
    I want to split the 3-word-name to update respectively the
    firstname,middlename,lastname columns.
    Can u help pls.

    Thanks a lot

  2. #2
    Join Date
    Apr 2003
    Posts
    353
    1 select substr('MARTIN SCOTT SMITH',1,(instr('MARTIN SCOTT SMITH',' ',1,1)-1)) "ONE",
    2 substr('MARTIN SCOTT SMITH',(instr('MARTIN SCOTT SMITH',' ',1,1)+1),
    3 instr('MARTIN SCOTT SMITH',' ',1,2)-(instr('MARTIN SCOTT SMITH',' ',1,1)+1)) "TWO",
    4* substr('MARTIN SCOTT SMITH',(instr('MARTIN SCOTT SMITH',' ',1,2)+1)) "THREE" from dual
    SQL> /

    ONE TWO THREE
    ------ ----- -----
    MARTIN SCOTT SMITH

    Replace the names with your column name

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    Multiple spaces if found between firstnamd and secondname & between
    second name and thirdname need to be replaced to single space.

  4. #4
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    ang galing...salamat po

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you can use this function

    Code:
    CREATE OR REPLACE FUNCTION AWK(P_STRING VARCHAR2, P_POSITION NUMBER, P_DELIMIT VARCHAR2)
    RETURN VARCHAR2
    AS
            type array is table of VARCHAR2(1000);
            l_array         array := array();
            l_string        VARCHAR2(1000) := p_string;
            l_pos           NUMBER;
            l_count         NUMBER;
    BEGIN
            LOOP
                    EXIT WHEN l_string is null;
                    l_pos := instr(l_string, p_delimit);
                    l_array.extend;
                    IF ( l_pos > 0 )
                    THEN
                            l_array(l_array.count) := trim(substr(l_string, 1, l_pos - 1));
                            l_string := substr(l_string, l_pos + 1);
                    ELSE
                            l_array(l_array.count) := trim(substr(l_string, 1));
                            l_string := NULL;
                    END IF;
            END LOOP;
            l_string := l_array(p_position);
    RETURN l_string;
    EXCEPTION
            WHEN SUBSCRIPT_BEYOND_COUNT
            THEN
                    RAISE_APPLICATION_ERROR(-20001, 'There arent '|| p_position || ' fields');
            WHEN COLLECTION_IS_NULL
            THEN
                    RAISE_APPLICATION_ERROR(-20002, 'Collection/Array not initialized');
            WHEN OTHERS
            THEN
                    RAISE;
    END AWK;
    /
    
    select * from names;
    
    FULLNAME                       FIRST_NAME MIDDLE_NAM LAST_NAME
    ------------------------------ ---------- ---------- ----------
    TOM TOM GO
    ALBERTO GONZALEZ VIANA
    
    update names
    set first_name  = awk(fullname, 1, ' '),
        middle_name = awk(fullname, 2, ' '),
        last_name   = awk(fullname, 3, ' ')
    
    2 rows updated.
    
    select * from names;
    
    FULLNAME                       FIRST_NAME MIDDLE_NAM LAST_NAME
    ------------------------------ ---------- ---------- ----------
    TOM TOM GO                     TOM        TOM        GO
    ALBERTO GONZALEZ VIANA         ALBERTO    GONZALEZ   VIANA

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    But what about
    STAN HERMAN VAN GUNDY?

    or

    RICHARD MICHAEL VAN DER HORST??
    Jeff Hunter

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks a lot guys for your help.

    But yes, I also encounter fullname with more than 3 words.
    How do u determine the number of words contained in each fullname?
    I have to select the maximum and then create a container column
    for each word.

    Thanks again

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    By counting the number of blanks (spaces) in a string? Or, if there is possibility to have more consecutive spaces in a string, by counting the occurrences of a "space, followed by a non-space character".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks friend jurij, wow...ur still up on sunday


  10. #10
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi again friends, can u pls help me to modify the program of
    engiri to accomodate > 3-word name. Im really puzzled how.
    There must be a formula somewhere...
    My question is if I assume to have a maximum of 6-word fullname,
    will the program run true also for a 3-word fullname

    Thanks again

Posting Permissions

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


Click Here to Expand Forum to Full Width