RIGHT, LEFT and CHARINDEX not performing as expected-CodePudding

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?

CodePudding user response：

I have tested your query and you do indeed have some formatting issues.

Rather than pick it apart, here is a simpler working alternative you can try. By using cross apply and a values construct you can abstract out the string splitting to leave a more easy to use select list of columns.

select name_bat, f.name FirstName, l.name LastName, Concat_ws(' ',f.name,l.name) Name_full
from t
cross apply (values( Left(name_bat,CharIndex(',',name_bat)-1 )))l(name)
cross apply (values( Replace(name_bat,Concat(l.name,', '),'') ))f(name)

See original and new version example

CodePudding user response：

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.