Hello,

I have a field called 'name' I want to split that into 'last_name' and 'first_name'. I know how to do this in PHP, but no idea how by just using mysql?

Example:

name: Smith John

I want to be:
name: Smith John
last_name: Smith
first_name: John

Does anybody know the sql statement necessary for this?

Thx.

    Assuming your variable name is called $fullname, you could do something like this...

    $fullname = "John Smith";
$splitname = explode(" ", $fullname);
$firstname = $splitname[0];
$lastname = $splitname[1];

echo $lastname.", ".$firstname;

// Output would be:
// Smith, John

      Oh wait, I just reread your post, and realize you already know this... Oh well, I will leave the post, so others can learn 😉 Sorry. I will look through the mySQL manual to see if this can be done, but I don't think it can using an SQL statement.

      Is there any particular reason you want to do this in an SQL statement anyhow? Seems to me, it would slow things down a bit during the actual query.

        Not to be a pain... but what was this for? Knowing my users (or at least the testers i work will) will try "John S Smith" then your kinda screwed. Is there a reason why you don't want to use two fields?

        Oh will just a thought

          The only reason I would like to know how it I'm at work and cannot access my home computer from here. I think I'm behind a big fat firewall at work and I've tried PCanywhere, Remotely Anywhere and VNC to try to access my home computer without any luck...

          This isn't vital that I know how to do this, but I can access phpMyadmin from work, so I am limited to sql statements from here as even Ftp won't work from here. (it does from home though.)

          That's the reasoning for my madness 🙂

            So what exactly is it that you are trying to do? Why are you trying to split the name to begin with? What does your last post about being behind a firewall have to do with anything 🙂

            What does any of this conversation have to do with your original post's title: "How to UPDATE mysql table by splitting a variable"

            Castle, you are right, I am looking at more of a controlled environment, where I as the admin would be inputting the names to begin with. Actually, if that were the case, they would already be split into 2 fields anyhow.

              When I originally made the database, I just had a name field. (my mistake.) So now I am trying to split the name into first name and last name in order to compare my database against the head office database. (which takes files in the format, last, first, address, city, phone).

              The reason I mentioned a firewall is I cannot upload any PHP scritp to my server from work. I can only do this from home.

              I looked at the recommended link above and will figure it out, but am still a bit confused...

                No propblem, I was just curious... I was looking at the string functions from that link, and I think you are still going to have to implement some php in order to do what you want... I just think it would be easier and more efficient in the long run.

                You may want to talk to your sys admins to open up the appropriate port on the firewall for you... Afterall, it sounds as though you are doing work related stuff anyhow, and the security risks are minimal.

                  2 months later

                  SELECT SUBSTRING_INDEX("SMITH JOHN"," ",1) AS LastName,SUBSTRING_INDEX("SMITH JOHN"," ",-1) AS FirstName

                  This is my solution to your answer.

                  I´ll try to do this using SUBSTRING, MID, LOCATE, ETC., but this function works better.

                  You can test this using mysqlcc, or mysql, just type this an execute de statement.

                  Bye.

                  Ramsore

                    Write a Reply...