separate a field into 2 fields

[renny]

Hello,

I have a database with field fullname (format: firstname lastname) and I would like to separate firstname and last name (which are separated by a space) into two different fields (fname and lname respectively). What is the best way to tackle this?

Your help is much appreciated.

-Renny

[mkili]

SUBSTRING(fullname, 1, LOCATE(' ',fullname) -1) as first

should do it for first name, its up to you to make it for surname

PS: Is it only me that I cannot see any images here?

Originally posted by renny
Hello,

I have a database with field fullname (format: firstname lastname) and I would like to separate firstname and last name (which are separated by a space) into two different fields (fname and lname respectively). What is the best way to tackle this?

Your help is much appreciated.

-Renny

[irishprogrammin]

if there are mistakes, I apologize, as I'm at a client site, and don't have access to test or anything...assuming you are using mysql, well, you get the point...

$sql = "select recordID, fullname from tablename";
$query = mysql_query($sql);
while ( $row = mysql_fetch_array($query) )
{
$currentrecord = $row["recordID"];
$fullname = $row["fullname"];
$pos = strpos($fullname, " ");
$length = strlen($fullname);
if ($pos === false)
{

$firstname = substr($fullname,0,$pos-1);
$lastname = substr($fullname,$pos,$length - $pos);
$sql2 = "update tablename set(firstname='$firstname',lastname='$lastname') " .
"where recordID = $currentrecord";
$query = mysql_query($sql2) or die("Failed because " . mysql_error() . " look: " . $sql2);
}
else
{
'something here in to account for $fullname not
having a space, either take the whole string as
first name, or ...'
}
}

enjoy!