SQL: Separate Column Value into Multiple Columns

Question

I have a table tblPerson and column fullname

--------------
fullname   
--------------
Garcia, John C.
Herdan, Stephen S.
Ubico, Ed Z.

I want to explode the fullname column in order to get the last, first and middle name and create 3 columns respectively.

I am using MS SQL Server 2008 R2

Output:

------------------------------------------------------------------------
   fullname              lname             fname               mname
------------------------------------------------------------------------
Garcia, John C.          Garcia,           John                C.
Herdan, Stephen S.       Herdan,           Stephen             S.
Ubico, Ed Z.             Ubico,            Ed                  Z.

Thank You in Advance

What have you tried so far? Please also have a look at: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — mario.van.zadel, Jan 06 '16 at 06:14

Pedram · Answer 1 · 2016-01-06T06:58:01.493

2

Easiest solution would be,

SELECT fullname,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,1)) AS lname
      ,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,2)) AS fname
      ,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,3)) AS mname
FROM   tblPerson

OR

SELECT fullname,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,1)) AS lname
      ,REVERSE(PARSENAME(REPLACE(REVERSE(fullname) ,' ' ,'.') ,2)) AS fname
      ,STUFF(fullname, 1, Len(fullname) +1- CHARINDEX(' ',Reverse(fullname)), '') mname
FROM   tblPerson

sql fiddle demo

edited Jan 06 '16 at 06:58

answered Jan 06 '16 at 06:28

Pedram

6,256
10
65
87

When I try this there is an extra comma after the last name and no dot after the mname. Easy fixes of course... – André Kops Jan 06 '16 at 06:32
have you checked my updated answer? btw you have shown your expected output that lname with "," at the end. – Pedram Jan 06 '16 at 06:33
I'm not the question asker... but you're right, I missed that he put the comma there... – André Kops Jan 06 '16 at 06:37
oh sorry I have not checked you are not question asker :D – Pedram Jan 06 '16 at 06:38

André Kops · Answer 2 · 2016-01-06T06:48:43.913

This one will support multi-word last and first names:

SELECT 
CASE WHEN fullname LIKE '%, %' THEN SUBSTRING(fullname, 1, CHARINDEX(', ', fullname)) ELSE fullname END AS lname,
CASE 
WHEN fullname LIKE '%, % %.' THEN SUBSTRING(fullname, CHARINDEX(', ', fullname)+2, LEN(fullname)-CHARINDEX(' ', REVERSE(fullname)) - CHARINDEX(', ', fullname)-1) 
WHEN fullname LIKE '%, %' THEN SUBSTRING(fullname, CHARINDEX(', ', fullname)+2, LEN(fullname)- CHARINDEX(', ', fullname)-1) 
END AS fname,
CASE WHEN fullname LIKE '%, % %.' THEN SUBSTRING(fullname, LEN(fullname)-CHARINDEX(' ', REVERSE(fullname)) + 2, 1000) END AS mname
FROM tblPerson

score 1 · Answer 3 · answered Jan 06 '16 at 06:50

Try;

;with data_tbl as (
    select [fullname],
    CHARINDEX(',', [fullname]) + 1 index_comma,
    CHARINDEX(' ', [fullname], CHARINDEX(',', [fullname]) + 2) index_space,
    len([fullname]) len_name
    from tblPerson
    --where [fullname] like '%, % %'
)
select 
    [fullname],
    left([fullname], index_comma - 1) lname,
    substring([fullname], index_comma, index_space - index_comma) fname,
    substring([fullname], index_space, len_name) mname
from data_tbl

sql fiddle demo

If you add the where condition where [fullname] like '%, % %' then it will ignore all the fullname that is not in the fromat %, % %

SQL: Separate Column Value into Multiple Columns

3 Answers3