Manipulate Data SQL Server

Question

I am trying to manipulate a piece if data I am pulling in. Right now the name is pulling in as First Name Last Name I would like to pull it in as Last Name, First Name.

Query

SELECT name from [TBL_Customers] order by name

The name column has the first and last name contained in it, with a space separating the two.

What is the best practice to achieve Last Name, First Name?

`"What is the best practice"` - Honestly, the "best practice" is to treat single values as single values and multiple values as multiple values. If a "name" should have two distinct parts, store it in two distinct columns. Parsing names from a single string value is a non-trivial task. What are you going to do with names which contain more than 2 parts? Or where any given part itself has a space in it? Also, an obligatory link: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ — David, Aug 10 '15 at 17:33
possible duplicate of [T-SQL substring - separating first and last name](http://stackoverflow.com/questions/10921400/t-sql-substring-separating-first-and-last-name) — mwigdahl, Aug 10 '15 at 17:43

score 0 · Accepted Answer · answered Aug 10 '15 at 17:55

You could simply use SQL Server's built in substring, charindex and left functions to do this:

SELECT NAME
      ,substring(NAME, charindex(' ', NAME) + 1, LEN(NAME) - CHARINDEX(' ', NAME) + 1) 
       + ', ' + left(NAME, charindex(' ', NAME) - 1) as new_name
FROM TBL_Customers
order by name;

SQL Fiddle Demo

Manipulate Data SQL Server

1 Answers1