The column lists the name as "Doe, John A" I need this to be separated into 2 columns.

Last Name | First Name
Doe       | John A

Heres the code I have but I need it to remove the "," that it outputs

LTRIM(RTRIM(SUBSTRING(Staff_Name, 0, CHARINDEX(' ', Staff_Name)))) As [Physician First Name],
 LTRIM(RTRIM(SUBSTRING(Staff_Name, CHARINDEX(' ', Staff_Name) 1, 8000)))As [Physician Last Name]

Here is what the code is currently outputting:

Last Name | First Name    
Doe,      | John A

CodePudding user response：

I think you can use , instead of space to remove the comma.

CHARINDEX(',', Staff_Name)