Monday 18 May 2015

Split a single column value to multiple columns with Space

Introduction
In this article, I will explain how to splitting a single column value into multiple with space and stored into Table
Let’s start off with an example including step by step operations.

Steps:
• Create a table with the following structure
Create table Emp_Data
(Id int identity(1,1),

FullName varchar(50))

• Insert the values into the table
insert into Emp_Data values('Ken J')
insert into Emp_Data values('Terri Lee')
insert into Emp_Data values('Rob M')
insert into Emp_Data values('Diane L')
insert into Emp_Data values('Janice M')
insert into Emp_Data values('Kevin F')

Id
FullName
1
Ken J
2
Terri Lee
3
Rob M
4
Diane L
5
Janice M
6
Kevin F

• In the above table FullName value into split into two names like first name and last name, stored into same table
• Add two columns by using the below command
alter table Emp_Data add firstname varchar(20),lastname varchar(20)

&

• Update the Firstname and lastname from fullname by using below command
update emp_data  set firstname =(LEFT(FullName, Charindex(' ', FullName) - 1)) FROM  Emp_Data b where id=b.id


update emp_data  set lastname =(RIGHT(FullName, Charindex(' ', Reverse(FullName)) - 1)) FROM  Emp_Data b where id=b.id

• Final Output is
Id
FullName
firstname
lastname
1
Ken J
Ken
J
2
Terri Lee
Terri
Lee
3
Rob M
Rob
M
4
Diane L
Diane
L
5
Janice M
Janice
M
6
Kevin F
Kevin
F

Let me know what you think about this article.

2 comments: