How To Format Simple String Output From SQL Server SELECT Query

Question

We are using SQL Server 2016.

I have a column called TrainerName that contains "Bob Smith". I would like to perform a SELECT query that would return this column looking like "Smith, Bob". How can I do this with the SELECT statement?

One small caveat is that the column might contain say "Mary Jane Smith" and I need to have this come out as "Smith, Mary Jane".

Thanks for any help with this.

Answer 1

I hope you're doing this so that you can store the data properly into different columns for first and last name. In this case, it can be a good first step, as there are fewer changes you need to make when you go back and manually clean up names that don't meet your expectations. If this is not your purpose, you're going to have many headaches anytime you have a requirement come up that involves names.

Assuming this is your purpose, then find the position of that last space, split the first name from the last name using it, and then recombine.

Here's some sample data:

declare @trainers table (trainer varchar(255));
insert @trainers values ('Bob Smith'), ('Mary Jane Smith');

And here's a query that splits as you desire:

select      t.trainer,
            name = names.lastName + ', ' + names.firstName
from        @trainers t
cross apply (select 
                value = len(trainer) - charindex(' ', reverse(trainer))
            ) lastSpace
cross apply (select 
                firstName = left(trainer, lastSpace.value),
                lastName = substring(trainer, lastSpace.value + 2, len(trainer) - lastSpace.value)
            ) names

Note that there's not much room for error. If there is a space in the last name, you need even more complex logic.