RIGHT, LEFT and CHARINDEX not performing as expected

I am trying to reorder a column of names that is in the form lastname, firstname to firstname lastname. The original column is NAME_BAT and I have confirmed there are no leading or trailing spaces. Here is my SQL code:

SELECT TOP (100) NAME_BAT
    , LTRIM(RIGHT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1)) AS FIRST_NAME
    , RTRIM(LEFT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1)) AS LAST_NAME
    , LTRIM(RIGHT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1)) + ' ' + RTRIM(LEFT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1)) AS NAME_FULL
FROM pitch_aggregate
;

and here is a screenshot of the output:

Output from SQL query

Why are the first four rows perfect, then row 5 includes a leading comma and space before the name, and in row 8 the first name has the first two letter cut off?

Take

LTRIM(RIGHT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1)) AS FIRST_NAME

Presume data like Casanova, Bob

CHARINDEX(', ', NAME_BAT) returns 9

RIGHT(NAME_BAT, CHARINDEX(', ', NAME_BAT) - 1) is the rightmost 9-1 = 8 characters, which is nova, Bob

(All your sample/testing firs and last names are nearly the same length.) I'm guessing this is not what you really want?

Now, something like

SUBSTRING(NAME_BAT, CHARINDEX(', ', NAME_BAT) + 2, 100)

might be what you're looking for. Me, I see it as a prime example of why SQL is a poor place to implement string manipulations.