Change John Smith to Smith, John

Question

Change John Smith to Smith, John

Jody Claggett-376930

Ten Centuries

Points: 1152
More actions
December 4, 2008 at 1:26 pm

#125545

I have a list of names like this:
John Smith
Bob Johnson
Earl Time
I need convert these to this:
Smith, John
Johnson, Bob
Time, Earl
How can I do this through T-SQL?
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Where does the list reside? Do you ever have a middle name/initial or Suffix? Ideally the database design would change to have separate columns for first, middle, and last names then you concatenate however you want.

Declare @table table(name varchar(50))

Insert into @table

Select

'John Smith' Union Select

'Bob Johnson'Union Select

'Earl Time'

Select

charindex(' ', name),

Substring(name, 0, charindex(' ', name)) as first_name,

Substring(name, charindex(' ', name) + 1, len(name)) as last_name ,

Substring(name, charindex(' ', name) + 1, len(name)) + ', ' + Substring(name, 0, charindex(' ', name)) as full_name ,

name

From

@table

How's that?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

This one deserves some thinking through/ahead

What's the rationale behind this swap? What if you have to swap back in the future?

Will it be easier to split them into a FirstName, MiddleName, LastName columns now?

You need to deal with the cases with middle names anyway

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

noeld SSC Guru Points: 96590 More actions · Answer 3

This kind of manipulation is tricky when you find people with more than one word in their name like " Kate van der Mullen"

* Noel

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 4

Thanks everyone. Jack's solution looks like it is going to work for me.

I don't have the ideal situation in that the field contains the full name of the employee and the first and last names are not in seperate columns. That being said, however this is only a one time data cleansing and update. Going forward, the names will be stored in the new format.

Before this is done, I will backup the existing data and store this with both versions. So if I need to revert back, I can.

_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 5

I'd be sure to check data quality with this. It appears to work, but Kendall Van Dyke might not work well, as perhaps John A. Smith.

You want to look for those exceptions, and then perhaps do some additional handling on them.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Think about doing a query to list names contain more than two strings (more than one space '% % %'). You will want to change those individually.

Billy Bob Thornton

John Q. Public

Reggie Rucker Jr.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills