Convert LastName, FirstName, Middle Intial to FirstName Last Name

[plextor]

CR10
SQL 2000

I have a field in SQL that has the employee name as LastName, FirstName Middle Intial, I would like to convert it to FirstName LastName and remove the Middle intial. Any ideas on how to create a formula to do so.

Thanks,
Brian

 

[kskid]

See thread767-879525

Cheers,
-LW

 

[vidru]

This might do it, but if your data is inconsistent, this may generate errors:

Split({Table.Field}, " ")[2] + " " + Replace(Split({Table.Field}, " ")[1], ",", "")

As long as there are at least two "fields" separated by a space, the above will put the second element (FirstName) first, and the first element (LastName) second.

-dave

 

[plextor]

Thanks for the reply's.

I ran into a few problems with both formulas.

Formula One:
I couln't figure out how to use the TheName array after creating the value.
" whileprintingrecords;
stringvar array TheNames := split({table.field}," ");
numbervar NumberOfNames := ubound(TheNames);"

Formula Two:
I get the following error with the script below: A subscript must be between 1 and the size of the array:
"Split({Table.Field}, " ")[2] + " " + Replace(Split({Table.Field}, " ")[1], ",", "")"

Thanks again,

Brian

 

[lbass]

The first formula should be:

whileprintingrecords;
stringvar array TheNames := split({table.field}," ");
numbervar NumberOfNames := ubound(TheNames);
TheNames[1];//change the "1" to 2, etc. for other members of the array

The second formula should work as is unless you have cases where you have a missing first or last name. Try:

if ubound(split({table.field}," ") > 1 then
Split({Table.Field}, " ")[2] + " " + Replace(Split({Table.Field}, " ")[1], ",", "") else
{table.field}

-LB

 

[synapsevampire]

Just so you're aware, storing names in one field is a sign of bad database design as last names can have more than one name, as can first names, and some people do not have middle names, and then there's jr's, and Dr. and...

So testing will have to be performed for these conditions.

The above solutions make incorrect assumptions, such as a last name will never have a space in them, etc.

Try:

whileprintingrecords;
stringvar firstname:=trim(mid({table.name},instr({table.name},",")+1));
// so far we have firstname and middle init combined
stringvar lastname:=trim(left({table.name},instr({table.name},",")-1));
// we have the last name in it's entirety ok
// now we fix the first name as best we can:
// assuming it has more than one part and there's
// a single character
// on the end, it's probably the middle initial
if ubound(split(firstname," ")) > 1 and
(left(right(firstname,2),1) = " "
then
firstname:=left(firstname,len(firstname)-2);
firstname&" "&lastname

Should work pretty well, additional coding may be required.

You should start leaving the want ads on the responsible dba's desk

-k

 

[plextor]

Thanks you everyone. They all worked perfectly.

If you think the Name field is bad you should see the rest of the database design. Apparently the names are downloaded from our messy Active Directory, then smashed into a single field for our Altiris CRM system. Trying to design reports off a crapy database is damn hard.....

Thanks again.