SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL to Split a Column

    Hi All,

    Can anyone help me construct a MySQL command that will look at a column (in my case name) and select the last name out and leave the first name in the column and then add the last name to a different column.

    The structure of the name column is: lastname, firstname

    I would like the name column to keep the firstname but put the lastname into a new column already defined as lastname. (without the comma)

    Any help would be appreciated.

    Thanks,

    Rick

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    UPDATE daTable
       SET name = TRIM(SUBSTRING_INDEX(name,',',-1))
        , lastname = SUBSTRING_INDEX(name,',',1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you sir,

    The only issue was that the code you supplied put the same value in both fields but I was able to discern what you were doing and with 2 MySQL commands was able to accomplish the task.

    I need your book BTW, so I'll order it today from Sitepoint.

    Thanks again.

    Rick

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, whaddya know, i bamboozled myself

    the docs are quite clear about it --
    Quote Originally Posted by da manual
    UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:

    mysql> UPDATE persondata SET age=age*2, age=age+1;
    so instead of what i gave you, it should have been the other way around:
    Code:
    UPDATE names
       SET lastname = SUBSTRING_INDEX(name,',',1)
         , name = TRIM(SUBSTRING_INDEX(name,',',-1));
    see the difference?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I sure do. And I do appreciate your help.

    I did order your book for my library.

    The Sitepoint books are the best instructional guides on the market, I'm sure yours will be right up there with the other 11 that I have.

    Thanks to all of the contributors of Sitepoint. I have grown tremendously since I discovered you guys and gals.

    To Your Success,

    Rick


Bookmarks

Posting Permissions

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