I am creating a query to retrieve information for a report. However I need to break up a personnel column to have the format 'First Name, Surname'.
I can get the first name ok but I am having mixed results trying to get the surname especially if the name is just initials.
LEFT(p.Person1, CASE WHEN charindex(' ', p.Person1) = 0 THEN LEN(p.Person1) ELSE charindex(' ', p.Person1) - 1 END) as PersonWithComma
FROM PersonnelTable1 AS p
The column p.Person1 has nulls and the surnames are sometimes just initials. E.g. 'Peter G F'
Actually its the surnames where it has more than one space that is causing me the biggest headaches.
I'm not a programmer so not sure what is the best way to do this. E.g. I believe functions can cause a bit more of a performance hit?
However if the data is as messy as it appears to be do not expect SQL to automagically make it clean.