t-sql splitting name column

I'm using sql server 2008

I have  table that looks like this:
table
Here is the script to create my table

USE [Northwind]
GO
/****** Object:  Table [dbo].[EmployeesTest]    Script Date: 11/23/2016 3:01:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeesTest](
	[EmployeeID] [int] NOT NULL,
	[FullName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (1, N'Nancy Davolio')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (2, N'Andrew W Fuller Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (3, N'Janet Leverling')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (4, N'Margaret Peacock')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (5, N'Steven Buchanan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (6, N'Michael J Suyama Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert T King III')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (8, N'Laura Callahan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (9, N'Anne Dodsworth')
GO

Open in new window


If you notice, the FullName column it contains a string. The string is made up of FirstName MiddleInitial LastName Suffix.

How do I split the FullName column into the four parts and place each in it's own column.

SELECT [EmployeeID]
             ,[FullName]
             -- get FirstName part from the FullName column and place in column called FirstName

             -- get MiddleInitial part from the FullName column and place in column called MiddleInitial

             -- get LastName part from the FullName column and place in column called LastName

             -- get Suffix part from the FullName column and place in column called Suffix

FROM [Northwind].[dbo].[EmployeesTest]


Anyone know the best way to split fullname column?
LVL 1
maqskywalkerAsked:
Who is Participating?
 
skullnobrainsConnect With a Mentor Commented:
if your data is more complex and the above is not enough, you may like this approach

use a bunch of successive regular expressions with cases you do know how to handle and a catchall or basic algo involving a dictionnary lookup to handle the rest. this also makes it much easier to handle international formats

try to start with this

the ruleset is first match wins

^(.*)\s+([A-Z])\s+(\S+)\s+([IVXC]+|[0-9]+|[Jj][Rr])$ --> has middle initial and suffix
^(.*)\s+([A-Z])\s+(\S+)\s+()$ --> has middle initial but no suffix
^(.*)\s+()\s+(\S+)\s+([IVXC]+|[0-9]+|[Jj][Rr])$ --> no middle initial but has suffix
...                               --> this is ( likely ) where you add more rules as needed
^(.*)\s+()\s+(\S+)\s+()$ --> catchall : first word is the first name, rest is the second name

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Sample output please?
0
 
ste5anSenior DeveloperCommented:
Well, it's not possible. Cause there are names first names (double names without hyphen) as well as last names (van der Beek) which contain spaces. Also some Asian names have a reverse order.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try..

SELECT EmployeeID,FullName, FirstName,Initial , REPLACE(LName,CASE WHEN rt > 0 THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END,'') LastName, CASE WHEN rt > 0 THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END Suffix FROM 
(
	SELECT *, RTRIM(LTRIM(REPLACE(LastName,Initial,''))) LName , CHARINDEX(N' ', RTRIM(LTRIM(REPLACE(LastName,Initial,''))),0) rt
	FROM 
	(
		SELECT *, SUBSTRING(RTRIM(LTRIM(LastName)) , 0, CHARINDEX(N' ',RTRIM(LTRIM(LastName)),1) ) Initial 
		FROM 
		(
			SELECT *, SUBSTRING(FullName , 0, CHARINDEX(N' ',FullName,1) ) FirstName				
					, SUBSTRING(FullName , CHARINDEX(N' ',FullName,1) , DATALENGTH(FullName) ) LastName
			FROM [EmployeesTest]
		)l
	)n
)b

Open in new window



Output

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

EmployeeID  FullName                                           FirstName                                          Initial                                            LastName                                                                                                                                                                                                                                                         Suffix
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           Nancy Davolio                                      Nancy                                                                                                 Davolio                                                                                                                                                                                                                                                          
2           Andrew W Fuller Jr                                 Andrew                                             W                                                  Fuller                                                                                                                                                                                                                                                            Jr
3           Janet Leverling                                    Janet                                                                                                 Leverling                                                                                                                                                                                                                                                        
4           Margaret Peacock                                   Margaret                                                                                              Peacock                                                                                                                                                                                                                                                          
5           Steven Buchanan                                    Steven                                                                                                Buchanan                                                                                                                                                                                                                                                         
6           Michael J Suyama Jr                                Michael                                            J                                                  Suyama                                                                                                                                                                                                                                                            r
7           Robert T King III                                  Robert                                             T                                                  King                                                                                                                                                                                                                                                              III
8           Laura Callahan                                     Laura                                                                                                 Callahan                                                                                                                                                                                                                                                         
9           Anne Dodsworth                                     Anne                                                                                                  Dodsworth                                                                                                                                                                                                                                                        

(9 row(s) affected)

Open in new window


Hope it helps !!
0
 
maqskywalkerAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.