• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

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?
0
maqskywalker
Asked:
maqskywalker
2 Solutions
 
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 KumarDatabase 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
 
skullnobrainsCommented:
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
 
maqskywalkerAuthor Commented:
thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now