maqskywalker
asked on
t-sql splitting string column
i'm using the northwind sql server database
when I use a query like this I am able to concatenate two columns to create the FullName column
SELECT [EmployeeID]
,[LastName]
,[FirstName]
,[FirstName] + ' ' + [LastName] AS FullName
FROM [Northwind].[dbo].[Employe es]
It looks like this:
So now I'm trying to do the reverse.
I have a table that looks like this. The FullName column is a varchar column
This is the script for this table
I'm trying to do something like this.
SELECT [EmployeeID]
,[FullName]
-- get the first part of the FullName column and create a column called FirstName
-- get the second part of the FullName column and create a column called LastName
FROM [Northwind].[dbo].[Employe esTest]
anyone know the syntax to split the FullName column into two?
when I use a query like this I am able to concatenate two columns to create the FullName column
SELECT [EmployeeID]
,[LastName]
,[FirstName]
,[FirstName] + ' ' + [LastName] AS FullName
FROM [Northwind].[dbo].[Employe
It looks like this:
So now I'm trying to do the reverse.
I have a table that looks like this. The FullName column is a varchar column
This is the script for this table
USE [Northwind]
GO
/****** Object: Table [dbo].[EmployeesTest] Script Date: 11/23/2016 1:29:56 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 Fuller')
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 Suyama')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert King')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (8, N'Laura Callahan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (9, N'Anne Dodsworth')
GO
I'm trying to do something like this.
SELECT [EmployeeID]
,[FullName]
-- get the first part of the FullName column and create a column called FirstName
-- get the second part of the FullName column and create a column called LastName
FROM [Northwind].[dbo].[Employe
anyone know the syntax to split the FullName column into two?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
In the accepted solution you may want to put an ltrim() around the substring function to remove the leading space.
ASKER