Solved

t-sql splitting string column

Posted on 2016-11-23
5
27 Views
Last Modified: 2016-11-23
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].[Employees]

It looks like this:

query 1
So now I'm trying to do the reverse.

I have a table that looks like this. The FullName column is a varchar column

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

Open in new window



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].[EmployeesTest]


anyone know the syntax to split the FullName column into two?
0
Comment
Question by:maqskywalker
5 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 125 total points
ID: 41898798
You're problems start when you have a name like "John Van Renan" or something like that. this is because surnames can be double barrelled and not be hyphenated. You have to make a decision. In general, it is very rare for a first name to be in two parts without a hyphen. You may get something like "Jean Paul Gaultier" which you may feel that the first name is "Jean Paul", so how do you cater for everything. This only takes into account Anglicised names as well. You will have other problems to deal with when considering non Anglicised names.

That said, I've used basic SQL like this:

declare @fn nvarchar(100)
select @fn = 'john smith'
select rtrim(left(@fn, charindex(' ', @fn))) as firstname,
       replace(@fn, left(@fn, charindex(' ', @fn)), '') as lastname

Open in new window

0
 
LVL 21

Assisted Solution

by:Tapan Pattanaik
Tapan Pattanaik earned 125 total points
ID: 41898892
Hi maqskywalker,  

Please run  the below query.

Select EmployeeID, PARSENAME (Replace(FullName,' ','.'),1) LastName,
PARSENAME (Replace(FullName,' ','.'),2) FirstName            
From EmployeesTes

Regards,
Tapan Pattanaik
0
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points
ID: 41898903
Try this ..

Do not use PARSENAME -- It is available for SQL 2012+ and your Db is SQL Server 2008

SELECT EmployeeID, SUBSTRING(FullName , 0, CHARINDEX(N' ',FullName,1) ) FirstName 
		         , SUBSTRING(FullName , CHARINDEX(N' ',FullName,1) , DATALENGTH(FullName) ) LastName
FROM [EmployeesTest]

Open in new window


Output
-------------

EmployeeID  FirstName                                          LastName
----------- -------------------------------------------------- --------------------------------------------------
1           Nancy                                               Davolio
2           Andrew                                              Fuller
3           Janet                                               Leverling
4           Margaret                                            Peacock
5           Steven                                              Buchanan
6           Michael                                             Suyama
7           Robert                                              King
8           Laura                                               Callahan
9           Anne                                                Dodsworth

(9 row(s) affected)

Open in new window


Hope it helps !!
0
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41898931
thanks
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41898945
In the accepted solution you may want to put an ltrim() around the substring function to remove the leading space.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

10 Experts available now in Live!

Get 1:1 Help Now