Improve company productivity with a Business Account.Sign Up

x
?
Solved

t-sql splitting string column

Posted on 2016-11-23
5
Medium Priority
?
94 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 500 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 22

Assisted Solution

by:Tapan Pattanaik
Tapan Pattanaik earned 500 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 38

Accepted Solution

by:
Pawan Kumar earned 1000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

606 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