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.