Data manipulation in Full Name column
-
The current way the name column is set is last,first,middle seperated by commas.
How would I create 3 seperate columns to show the same information with the data from the full name column? This is a SQL 2000 database
-
Check out this posting and see if it is similar to your needs.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=200907
I wasn't born stupid - I had to study.
-
I checked out the article Does this mean that I have to enter after VALUES each name? There is 18k of them
--Table with values to be parsed
CREATE TABLE OldNames(FullName varchar(50))
INSERT INTO OldNames (FullName) VALUES('Mr. Jones Eric')
INSERT INTO OldNames (FullName) VALUES('Mr Doe John')
INSERT INTO OldNames (FullName) VALUES('Mrs. Smith Janet')
INSERT INTO OldNames (FullName) VALUES('Mrs Johnson Virginia')
INSERT INTO OldNames (FullName) VALUES('Miss Reynolds Miranda')
INSERT INTO OldNames (FullName) VALUES('Miss. Proctor Emily')
--Parse the FullName field into Title, LastName and FirstName fields
-- and insert the values into a new table
SELECT Left(FullName, CharIndex(' ', FullName) -1) AS Title,
Right(FullName, CharIndex(' ',Reverse(FullName))- 1) AS LastName,
SubString(FullName, CharIndex(' ', FullName)+ 1,
(CharIndex(' ',FullName, CharIndex(' ',FullName)+1))-(CharIndex(' ', FullName) )) AS FirstName
INTO NewNames
FROM OldNames
--Check results
select * from OldNames
select * from NewNames
-
Steve,
How sure are you that ALL your data is following the Last,First, middle format?
And if you feel confident how will persons with two or more Names or Surnames are handled?
Can you post some examples?
* Noel -
Exactly, noeld!! Those were questions raised in that post.
This type of solution, (and we can refine the code if you need that) MUST have that format be consistent.
Please post some examples and determine if that format is ALWAYS followed (you can write exception code into this if a consistent alteration occurs...).
I wasn't born stupid - I had to study.
-
Here are some examples
BROWN, T
Saurbaugh, Nancy
Clingen, Faith S
BERGER, JEREMY SETH
WIGGINS, MONICA
BOBO, JAMES R
Koopman Deborah
EVANS, AMY F
KRIETE, ARTHUR
CARDWELL, RODNEY
BESCHTA, SANDRA
Davidson, Matthew R
Bukowski , James Jr
Wilhelm, Tara
JOSHI, SHREELEKHA, S
STEPHANYS, CORAY
HALL, REGINA
Fleming, Jeannette
KEMBLOWSKI, MATT
KHAN, RANA
SCHWAEBER,MICHAEL
MCQUARRIE, JANICE
RUSSELL, MICHELLE
REARDON, ANDREW
KURZ, ROXANNE
THUNBERG, SHARON L.
Colar, Floyd C
STRAINIS, KIM A.
STEPINSKA, AKI
CADMUS, LAURA
CADMUS, DEREK
NWAJEI, CATHERINE
MENZIA, RICHARD, M.
TANNER, TRISTAN MARIE
Kimball, Ronald R.
GREEN, ADELE MAE
QUIRION, MICHAEL
Grece, Kari A
Finnemore, Bryan P
Thampi, Nivin
Zacharski, Patricia
SAYRE, CAROLYN
NEUSNER, SAMUEL
MARTIN, SUZANNAH
SMITH, JAIME A.
CULVER, CHRISTOPHER-not hire
PETRESCU, ANDREI
ELLIS, JOHN
Turnbull, Tom
Marks, Alison
DODGE, MARK D.
JESSICA, LODI
Collingwood, Andrew
Arciaga, Dennis
GRGORY, JAMES
Blake, Elizabeth A
COHEN, AMANDA
ANASTASIA, MICHAEL
FURLONG, CRAIG M
POWERS, ANN MARIE
Harlow, Jeanne
Tennant, William J
SAFFAREWICH, STEPHEN
ORTIZ JR., WILBE
Arvanites, Christopher
Ikram, Mohammed A
Maisel, Heather
GILMAN, ADAM
CANCILLA, ROBERT
Bernick, Cindy Lee
DYER, ELIZABETH
Smith, Adam P
KULIG, AURELIA
HASLAM, PETER
Krezinski, Michael E
WOUBSHET, FIKIRTE
Fortune, JoAnn
AGNEW, MELISSA
Kazmi, Ansar H
Desai, Amrita A
GRAY, CHRISTOPHER
Xiong, Linda
KIM, LORI
CZERNICH, JASON
BRUNELL, ELIZABETH
Shiau, Daniel
DONG, ANH
Janczurewicz, Joanna
RUTHERFORD, SCOTT A.
ZAPATA, JOSE
Mastracci, Richard M
KARLIN, ANN
PATELLA, RYAN
KULIG, ERIKA L.
Johnson, Betty
IHEDIGBO, NATHANIEL
Curiale, Benjamin
Prabhu, Shweta
GRIFFIN, JOHN
PICONE, DANIEL
SHAHREEN, NADIYA
TODOROVA, VICKY
BLATTNER, KATHERINE
COOK, KARIN H.
Keith, Joseph
KORALIK, JUDY
Williams, Audery F
CLEPPER, AINKA
Roeder, Michael
TURNER, SONYA
KIND, BROOKE
Zunzanyika, Tichawona
Bacon, Frederic S
CARSON, NANCY
JOHNSON, ELAYNE
LEFAVE, GREGORY E
JACOB, GEORGE
SALGADO, MICHELLE
Ganatos, Taryn A
BOROWSKI, SUSAN
ROCK, MELISSA
GRASSO, MICHAEL
-
As you probably can see there is no uniformity in for example Jr sometimes was set on the first name, sometimes on the Last ... some names have even stuff in them that do not belong like --not hired ...
I could write a Query for most of those cases but I still will not be sure that everything is going to be correct and to be 100% sure you will still have to walk through them one by one!!
* Noel -
Steve,
I checked out the article Does this mean that I have to enter after VALUES each name? There is 18k of them
The lines with VALUES in that post simply populated a table to be used as an example. They were not part of the solution, so to answer your question - No you don't need to enter them.
I haven't seen a post in a few days. Have you solved your problem?
If not, we can try a couple of things. Noeld rightly points out the difficulty in parsing this field where there is little consistency in the data.
If you end up with a column for each of the name parts you want, would it be possible for someone to visually inspect the results and correct the records that parse correctly?
If you absolutely have to have accurate data from a fully automated solution you will probably need to use some procedural code in a cursor (very slow) or write a program in VB.NET or C# to get it done. The last post in the thread referred to above listed some code that may work for you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply