Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Exclamation Unanswered: splitting a column into 2 or more columns

    Hello Guys,

    Currently, i have a column that is named ' FULL_NAME '. I need to split it into the following : First_Name, Middle_name, and Last_Name. I've been messing around with subster and instr functions, but the problem i'm facing is basically the data in the column kinda varies from one to anther. As in some have only 2 names ( e.g. hassan ali ), others have 3 names ( e.g. hassan Waiel ali ) and others have 4 names ( e.g. hassan waiel hassan ali ).

    What i want to do is put the first name ( hassan* waiel hassan ali ) in the FIRST_NAME col., and the last name ( hassan waiel hassan ali* ) in the LAST_NAME col. and anything in between ( hassan waiel* hassan* ali ) should be in the MIDDLE_NAME col. .

    Can anyone help or suggest something for me ?

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Code:
    update test 
        set first_name   = substr(full_name,1,instr(full_name,' '))
            ,last_name   = substr(full_name,instr(full_name,' ', - 1))
            ,middle_name = substr(full_name,instr(full_name,' ') + 1, instr(full_name,' ', -1) - instr(full_name,' '))
    ;
    Last edited by magicwand; 12-10-12 at 06:38. Reason: adjusting column names
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Dec 2012
    Posts
    2
    Quote Originally Posted by magicwand View Post
    Code:
    update test 
        set first_name   = substr(full_name,1,instr(full_name,' '))
            ,last_name   = substr(full_name,instr(full_name,' ', - 1))
            ,middle_name = substr(full_name,instr(full_name,' ') + 1, instr(full_name,' ', -1) - instr(full_name,' '))
    ;
    it worked pretty well, thanks allot man.

Posting Permissions

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