Solved

t-sql splitting name column

Posted on 2016-11-23
5
51 Views
Last Modified: 2016-11-23
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
Comment
Question by:maqskywalker
5 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41898948
Sample output please?
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41898976
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
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41898980
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
 
LVL 27

Accepted Solution

by:
skullnobrains earned 250 total points
ID: 41899020
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
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41899531
thanks
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question