Solved

t-sql splitting string column

Posted on 2016-11-23
5
42 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 24

Accepted Solution

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

21 Experts available now in Live!

Get 1:1 Help Now