maqskywalker
asked on
t-sql splitting name column
I'm using sql server 2008
I have table that looks like this:
Here is the script to create my table
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].[Employe esTest]
Anyone know the best way to split fullname column?
I have table that looks like this:
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
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].[Employe
Anyone know the best way to split fullname column?
Sample output please?
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks