Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    79

    Unanswered: Splitting fields

    There is a field in my table called “contact name” - It contains both first and last name.

    Now what I need to do is separate first and last name into two different fields, however, I have over 10,000 names and its simply too much to do it one at a time. Is there any way to have the computer do this for me. Basically, I want the first word until the first space separated into a different fields – For example:

    Original:
    Contact name: John Thomas Stone

    Result:
    First name: John
    Last Name: Thomas Stone

    Any help is greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select [Contact name]
    , instr( [Contact name] , ' ') as position_of_first_blank
    , left( [Contact name] , instr( [Contact name] , ' ') ) as [First name]
    , mid( [Contact name] , instr( [Contact name] , ' ') + 1 ) as [Last name]
    from namestable;

    tested on

    John Thomas Stone
    Foo Bar
    THISNAMENOBLANK
    this name starts with a blank
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Posts
    79
    Where does this code go?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Query > New > View > SQL View

    paste it in there and run the query


    rudy

Posting Permissions

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