Separating Name String

[shahid4162]

I have a coulmn in my ms-sql server table which contains first name and last name separated by a single space. I want to spearte them in two columns with first name in one column and last name in other. But my problem is that in some names the last name is in this format for example Allen Tate, Jr., so in this case I am supposed to pick up the whole Tate, Jr. as last name.

Any suggestions, how to write this sql statement.

Thanks.

[steadyguy]

You can do it using PHP:

$result = mssql_fetch_array($connectionResource);
extract($result);
    //this next line assumes you have a field called 'name' in the table,
$nameArray = explode(' ', $name);
    //now you have an indexed array with all the parts of the name
    //assign as appropriate, tacking on the ' Jr.' to the 'Tate, '
$firstName = $nameArray[0];
$lastName = "$nameArray[1] $nameArray[2]";

This isn't the most elegant, but it works ...

An even easier solution would be to add a row to your table: lastName, and change the name row to firstName.

[laserlight]

I suspect that you'll have to do it manually, since names and surnames do take on many forms.

As mentioned, store names and surnames as separate fields to begin with.