Convert lastname, firstname middleinitial to different columns in sql

Question

I currently have a column named PersonFullName. It has data in it like this:

PERSONFULLNAME  
Doe, John A  
Doe, Jane M  
Doe, Eric

I am wanting to get the data in this format:

LastName  -  FirstName  -  MI  
DOE    -     John    -     A  
DOE    -     Jane    -     M  
Doe    -     Eric

Below shows up fine, but if I add a middle initial, the middle initial lands in the First name column.

declare @last as varchar(20)
declare @first as varchar(20)
declare @mid as varchar(20)

declare @name varchar(100)
select @name = 'Leake, Angela'

set @last = substring(@name, 1,charindex(', ',@name)-1)
set @first = LTRIM(substring(@name, charindex(' ',@name),len(@name)-charindex(' ',@name)+1))
set @mid = RIGHT(@name, CHARINDEX(' ', REVERSE(@name))-1)
If (@mid=@first) SET @mid=''

select @last as 'Last Name', @first as 'First Name',@mid as 'Middle Name'

Select PERSONFULLNAME FROM Kronos

Tag your question with the database you are using, as well as attempts you have made to solve the problem. — Gordon Linoff, Jul 07 '16 at 17:37
Would this answer help? [How to split string into columns for a view](http://stackoverflow.com/questions/17150593/how-to-split-string-into-columns-for-a-view) — Okomikeruko, Jul 07 '16 at 17:37
Is your data really this simple, or do you need to account for additional spaces, suffixes, periods, hyphens, apostrophes, etc., not to mention multiple cultures? Any significantly large list of free-text names will have exceptions to virtually any logic you can come up with. — Tim Lehner, Jul 07 '16 at 17:42
I am working to join 2 tables together, but 1 table is formatted as the PERSONFULLNAME -which is standardized inputs, and the other table is formatted as LASTNAME FIRSTNAME MI -which is not standardized inputs. — Albert Vincent, Jul 07 '16 at 17:49
@AlbertVincent I would be careful when joining on name. There are a few Joseph A. Smiths out there. — Tim Lehner, Jul 07 '16 at 18:02
@TimLehner I know, and that is what has me terrified about this project. My job here is basically linking Multiple different databases together, and creating software to do automatic data analysis on the results. I have thought about having the free form db to realign the way they input data, but the manager doesn't want his team to have to do "MORE WORK..." — Albert Vincent, Jul 07 '16 at 18:07

score 1 · Answer 1 · edited Jul 07 '16 at 18:06

1

Yes okomikeruko that worked. I am still learning SQL, so I didn't know about that CTE.

The following did exactly what I needed...

WITH CTE AS
(
    SELECT  PERSONFULLNAME,
            LEN(PERSONFULLNAME)-LEN(REPLACE(PERSONFULLNAME,' ','')) N
    FROM Kronos
)
SELECT  PERSONFULLNAME,
        PARSENAME(REPLACE(PERSONFULLNAME,' ','.'),N) 'First Name',
        PARSENAME(REPLACE(PERSONFULLNAME,' ','.'),N+1) 'Last Name',
        PARSENAME(REPLACE(PERSONFULLNAME,' ','.'),N-1) 'MI'
FROM CTE

edited Jul 07 '16 at 18:06

sstan

35,425
6
48
66

answered Jul 07 '16 at 17:57

Albert Vincent

51
9

Is this the Kronos time keeping system? – S3S Jul 07 '16 at 18:25
Ok, I've played in that DB before and remember the name being split in the [PERSON] table. Not sure what your environment looks like but I was just curious. – S3S Jul 07 '16 at 18:38
I only have a linked server with execute credentials to an SP that they created for me. Due to HR/Finance SOX standards that is all that I am allowed to see. Also, the data was created in it's current format around a year ago. There are multiple automated programs that are built around the current formatting. So with the new system that I now have a linked server to, I am having to create my own formatting in my db to accommodate. – Albert Vincent Jul 07 '16 at 18:43
Ah I see. Always harder when they won't give you the keys to the kingdom. – S3S Jul 07 '16 at 18:49
I'm working on it lol. I currently have ALMOST EVERY system in our site linked to each other in 1 form or another. Our inventory system is all that is left lol – Albert Vincent Jul 07 '16 at 18:51

Convert lastname, firstname middleinitial to different columns in sql

1 Answers1