T-sql update name fields

Question

T-sql update name fields

Gary Andrade

Ten Centuries

Points: 1028
More actions
April 14, 2008 at 5:35 pm

#69917

I have a field that lives in a very wide table that is being used to hold contact information. The name field (name) holds first and last name. Another system that will share information needs to have the data in the format of last, first. Example: John Smith = Smith, John.
Can someone share a sample code or suggest a method to get me closer to converting my data inside of the table.
Thanks,
Gary

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Splitting free text names is a huge pain in the patoooti... For example, two-part first names, middle initials, and a host of other formatting sins (FullName being the first sin) are going to jump up and "byte" you... here's a start... notice the problems with two part first names and middle initials...

DECLARE @yourtable TABLE (Full_Name VARCHAR(30))

INSERT INTO @yourtable (Full_Name)

SELECT 'John Smith' UNION ALL

SELECT 'Jeff Moden' UNION ALL

SELECT 'Gary Andrade' UNION ALL

SELECT 'Harry S. Truman' UNION ALL

SELECT 'Jill St. John'

SELECT Full_Name AS OriginalFirstName,CHARINDEX(' ',Full_Name),

RIGHT(Full_Name,LEN(Full_Name)-CHARINDEX(' ',Full_Name))

+ ', ' + LEFT(Full_Name,CHARINDEX(' ',Full_Name)) AS ReorderedName

FROM @yourtable

(5 row(s) affected)

OriginalFirstName ReorderedName

------------------------------ ----------- --------------------------------------------------------------

John Smith 5 Smith, John

Jeff Moden 5 Moden, Jeff

Gary Andrade 5 Andrade, Gary

Harry S. Truman 6 S. Truman, Harry

Jill St. John 5 St. John, Jill

(5 row(s) affected)

There's certainly ways around this using a bunch of tricks, but I signed a nondisclosure notice for this one...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Jeff,

I have modified your code slightly.

----------------------

Select SUBSTRING(Full_Name,CHARINDEX(' ',Full_Name),LEN(Full_Name))+','

+SUBSTRING(Full_Name,1,CHARINDEX(' ',Full_Name)) AS ModifiedName

From #yourtable

---------------------

It works fine.

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

All you did was slow it down. 😛 RIGHT and LEFT are faster than SUBSTRING. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gary Andrade Ten Centuries Points: 1028 More actions · Answer 4

Gary Andrade

Ten Centuries

Points: 1028

April 15, 2008 at 10:48 am

#802699

Good stuff Thanks everyone!:D

karthik M One Orange Chip Points: 29139 More actions · Answer 5

All you did was slow it down. RIGHT and LEFT are faster than SUBSTRING.

Really Jeff ! Can u give me an example ?

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Set up a million row test like I taught you...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gary Andrade Ten Centuries Points: 1028 More actions · Answer 7

When I try your code example it works, when I apply the code to data in an existing table I get blank spaces and then a comma and then first name. I'm not sure why this is happening. Could you use a permanent table to see if you get the same results.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Gary,

The spaces you're getting is probably because of the data or datatype... For example, are you using CHAR or VARCHAR in the existing table? Are there trailing spaces in the data?

Could you post the CREATE statement for the table and supply some data from the table so I can troubleshoot a bit for you? See the URL in my signature for an easy way to supply the data in a format I can easily use.

Also, you can do a little troubleshooting of your own... see what you get when you run something like this...

SELECT '|'+yournamecolumn+'|'

FROM yourtable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gary Andrade Ten Centuries Points: 1028 More actions · Answer 9

---- Create table to test name conversion ----

CREATE TABLE [dbo].[A_table](

[FUll_Name] [char](40) NULL

) ON [PRIMARY]

---- Insert data into table ----

INSERT INTO A_table (Full_Name)

SELECT 'John Smith' UNION ALL

SELECT 'Jeff Moden' UNION ALL

SELECT 'Gary Andrade' UNION ALL

SELECT 'Harry S. Truman' UNION ALL

SELECT 'Jill St. John'

----Query to reorder the name -----

SELECT Full_Name AS OriginalFirstName,CHARINDEX(' ',Full_Name),

RIGHT(Full_Name,LEN(Full_Name)-CHARINDEX(' ',Full_Name))

+ ', ' + LEFT(Full_Name,CHARINDEX(' ',Full_Name)) AS ReorderedName

FROM A_table

---- output-----

OriginalFirstName ReorderedName

---------------------------------------- -----------------

John Smith 5 , John

Jeff Moden 5 , Jeff

Gary Andrade 5 , Gary

Harry S. Truman 6 , Harry

Jill St. John 5 , Jill

Gary Andrade Ten Centuries Points: 1028 More actions · Answer 10

One thing I forgot to mention was I am runing this on SQL Server 2005. Could this be the problem. Should I repost this to the other SQL Server 2005 forum? Aslo how did you get a seprate box with your code inside of the post?

Thanks.

Gary Andrade Ten Centuries Points: 1028 More actions · Answer 11

I finally got my code to work and the output to look like I wanted.

---code snipet---

SELECT RTRIM(SUBSTRING(Full_Name, CHARINDEX(' ', Full_Name, 1) + 1, DATALENGTH(Full_Name))) + ',' + ' ' +SUBSTRING(Full_Name 1, CHARINDEX(' ', Full_Name, 1)) as ModifiedName

Thanks for your help!:w00t:

Sergiy SSC Guru Points: 110208 More actions · Answer 12

You should pray very hard not to let Jean Claude Van Damme or any of his relations to be registered in you system.

People with French, Dutch, Spanish and Portuguese descent should be by any means kept away from your system.

_____________
Code for TallyGenerator