How can I show an employee name as "FirstName LastName"

Sage 300 CRE stores names in 'LastName; FirstName' format so that when data is sorted by name, it will be sorted by last name. This article covers how show names with 'FirstName LastName' format. This could apply to PR Employee Names, AP Vendor Names, AB Contact Names, and AR Contact Names (any name field that uses LastName;FirstName format).

In the example below, cell B2 would contain the employee name as LastName;FirstName (e.g., "Smith; John"). This formula will search for the location of the semi-colon and reverse the names. If no semi-colon is found, the name will be returned as-is.

=IF(SEARCH(";",B2)=0,B2,TRIM(MID(B2,SEARCH(";",B2)+1,30)) & " " & TRIM(LEFT(B2,SEARCH(";",B2)-1)))

In the example below, an expression is used within an SQL statement to accomplish the same task that the Excel formula above accomplishes. In this example, the query returns a list of Employee IDs and Names from the PR Employee table. The "ENAME" field is the employee name field that stores names in "LastName;FirstName" format.

SELECT
  "EMPID" AS "Employee",
  IF(LOCATE(';',"ENAME")=0,"ENAME",LTRIM(SUBSTRING("ENAME",LOCATE(';',"ENAME")+1,30)) + ' ' + RTRIM(LEFT("ENAME",LOCATE(';',"ENAME")-1))) AS "Employee Name"
FROM
  "MASTER_PRM_RECORD_1"