How to UPDATE mysql table by splitting a variable?

Hiccup

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.

DeltaRho2K

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

DeltaRho2K

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.

Castle

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

Hiccup

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 🙂

DeltaRho2K

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.

tomhath

Look at the string functions mysql offers. You should be able to do what you want with LOCATE, POSITION, INSTR, SUBSTRING, and MID (find the space, then split atart the substrings):

http://www.mysql.com/doc/en/String_functions.html

Hiccup

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...

DeltaRho2K

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.

ramsore

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