Solved

t-sql splitting name column

Posted on 2016-11-23
5
22 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 17

Expert Comment

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

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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 26

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now