Change string order - Sql server

Question

Hi All,

I am moving data from an old table to a SQL server new version and am struggling with something that hopefully someone can help with.

I have a column in the old table with 'EmployeeName' in the order firstname.lastname. In the new table we will be using a guid to identify the employees. The table with the guid's has the names in the order 'lastname, firstname' so my function that I wrote to pull the guid for the employee fails to match the names.

How can I in SQL server convert 'firstname.lastname' to 'lastname, firstname'?

here is the function I wrote:

ALTER FUNCTION [Wire].[udf_GetEmployeeGuid_FromName]
( @EmployeeName   VARCHAR(50)  -- format 'firstname.lastname')
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @EmployeeGuid     uniqueidentifier
SELECT @EmployeeGuid = GUID
FROM B.Employees  --format 'lastname, firstname'
WHERE LEFT(@EmployeeName, CHARINDEX('.', @EmployeeName)) = RIGHT([Name], LEN([Name]) - CHARINDEX(', ', [Name]))
RETURN IsNull(@EmployeeGuid, '00000000-0000-0000-0000-000000000000')
END

Answer 1

Here's a start:

You can get lastname from firstname.lastname like this:

RIGHT(EmployeeName, LEN(EmployeeName) - CHARINDEX('.', EmployeeName))

Similarly, you can get lastname from lastname, firstname like this:

LEFT((EmployeeName, CHARINDEX('.', EmployeeName))

You can concatenate the names and punctuation to get the string you want.

You might consider also using TRIM to eliminate blanks in the data.