Solved

t-sql splitting string column

Posted on 2016-11-23
5
58 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 28

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

837 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