Question: I have this column

```
NAME
John Stephenson
James Martin
Anna Corelia

```

How I can select this column to this?

```
NAME
Stephenson, John
Martin, James
Corelia, Anna 

```

Answer: 
One way

```
;with test(name) as (
    select 'John Stephenson' union all
    select 'James Martin' union all
    select 'Anna J. Corelia' union all
    select 'BOBBYTABLES'
)
select
case when charindex(' ', name) = 0 then name  
else right(name, charindex(' ', reverse(name)) - 1) + ', ' + substring(name, 1, len(name) - charindex(' ', reverse(name))) end
from test

(No column name)
Stephenson, John
Martin, James
Corelia, Anna J.
BOBBYTABLES

```
```
SELECT substring(NAME+', '+NAME,charindex(' ',NAME)+1,len(NAME)+1) FROM MyTable

```

This also turns `"Rip Van Winkle"` into `"Van Winkle, Rip"`

**[SQL Fiddle Demo](http://sqlfiddle.com/#!3/d4a53/1)**