Solved

t-sql splitting name column

Posted on 2016-11-23
5
41 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 25

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 25

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 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

22 Experts available now in Live!

Get 1:1 Help Now