?
Solved

t-sql splitting string column

Posted on 2016-11-23
5
Medium Priority
?
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 21

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 29

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

765 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