SSRS Reporting Expression to Split Full Name Field

Question

I've been trying to get this to behave with no luck.

The database base field only has FULL NAME field, which includes Middle initials on some cases and no middle initials in some other cases.

I've been trying to display LastName, FirstName by using this ssrs expression below: While this works in names that doesn't have middle initials or name on the field.... for those names that includes middle initials/name, we run into issues with displaying.

=Right(Fields!agent_name.Value,Len(Fields!agent_name.Value)-Instr(Fields!agent_name.Value," ")) & ", " & (Left(Fields!agent_name.Value,Instr(Fields!agent_name.Value," ")))

But it includes the Middle Initials to display first.. As an example:

If the fullname field is John S Doe, above expression displays as:

S Doe, John

What I need to display is:

Doe, John

How do I set my expression to get rid of the Middle initial/name to display?

I've done a great amount of research and tried many diff expressions but no luck.. thanks in advance.

Answer 1

Try using this expression:

=RIGHT(
Fields!agent_name.Value,LEN(Fields!agent_name.Value)-InStrRev(Fields!agent_name.Value," "))
 & ", " & LEFT(
 Fields!agent_name.Value,
 InStr(Fields!agent_name.Value," ")-1
)

It is a native SSRS solution however I recommend using custom code to get an maintainable solution.

UPDATE: Regex solution:

=System.Text.RegularExpressions.Regex.Replace(
  Fields!agent_name.Value,
  "(\w+)\s+(\w+\.*\s)?(\w+)",
  "$3, $1"
 )

Let me know if this helps.