Switching lastname,firstname to firstname.lastname in sql server

Question

I have the below data in a SQL Server 2014 column called EMP_CLASS_10_DESCR

OUELLETTE,MAXIME :845800
ANDERSON,ADRIEN :021252
MITRI,NATHALIA :687173

What I like to do is have the data represented in the following way by way of a query.

MAXIME.OUELLETTE
ADRIEN.ANDERSON
NATHALIA.MITRI

What I've done so far is

CASE WHEN CHARINDEX(':', EMP_CLASS_10_DESCR) 
                     = 0 THEN EMP_CLASS_10_DESCR ELSE LEFT(EMP_CLASS_10_DESCR, CHARINDEX(':', EMP_CLASS_10_DESCR) - 1) END AS FULL_NAME,

But all that did was give me the following

OUELLETTE,MAXIME
ANDERSON,ADRIEN
MITRI,NATHALIA

I still need to flip the names around and replace the , (comma) with a . (period)

Any help will be much appreciated. I've done some searches but couldn't find anything close. I'm still very new to SQL Server and just trying to learn.

Answer 1

You can use PARSENAME:

WITH CTE AS
(
    SELECT CASE 
              WHEN CHARINDEX(':',YourColumn) > 0 
              THEN RTRIM(LTRIM(LEFT(YourColumn,CHARINDEX(':',YourColumn)-1))) 
              ELSE YourColumn 
           END NewColumn
    FROM dbo.YourTable
)
SELECT CONCAT(PARSENAME(REPLACE(NewColumn,',','.'),1),'.',PARSENAME(REPLACE(NewColumn,',','.'),2))
FROM CTE;