Remove trailing comma

I have a table with names formatted as “Smith, Stan” and need to display them as “Stan Smith”. I was able to do that but it seems that the comma has been left behind and I need to get rid of it. This is what I currently have:

SELECT SUBSTRING(Name, CHARINDEX(',', Name) + 1, LEN(Name)) + ' ' +
SUBSTRING(Name, 1, CHARINDEX(',', Name)) AS NewName
FROM Employees

Answer

How about using replace as below

SELECT SUBSTRING(Name, CHARINDEX(',', Name) + 1, LEN(Name)) + ' ' +
REPLACE(SUBSTRING(Name, 1, CHARINDEX(',', Name)),',','') AS NewName
FROM Employees