This quick guide shows you how to split a column in SQL Server
This can be a very common problem, but with a few commands it is easily achieved
The guide shows you step by step how to split one column into multiple columns in SQL Server.
You can copy the code and work through it yourself to see how it works
Initial Set up
Let’s start with a list of fictional names
If you want to work through these steps just copy this code
CREATE TABLE [dbo].[Names_Test]([Full_Name] [varchar](50) NOT NULL ) ON [PRIMARY]; insert into Names_Test Values ('Ivan Smith'); insert into Names_Test Values ('Kenya Villareal'); insert into Names_Test Values ('Simeon Hancock'); insert into Names_Test Values ('Mercedes Vaughan'); insert into Names_Test Values ('Frank Smith'); insert into Names_Test Values ('Lee Mooney');
After running this code you will have a new table called [Names_Test], and it will be populated with 6 names
Step 1
The first step is to find the position of the delimiter,
In our case it’s a space
select Full_Name
, Charindex(‘ ‘, Full_Name) as [Space Position]
from Names_Test
CHARINDEX tells us the character position of the space
Step 2
Next, we need to find the length of the full name
len(name) as [Name Length]
The LEN command tells us the total number of characters in the full name
adding this as a column to our SQL gives us
select Full_Name
, Charindex(‘ ‘, Full_Name) as [Space Position]
, len(Full_Name) as [Name Length]
from Names_Test
Step 3
The next step is to find the characters that make up the first name, by finding everything to the left of the space
left(Full_Name,(Charindex(‘ ‘, Full_Name)-1)) as [First Name]
This takes the space position of 5 in the first example, subtracts 1 to remove the space itself, and returns the first 4 characters
Step 4
To find the last name, we want everything to the right of the space
substring(Full_Name,(Charindex(' ', Full_Name)+1), (len(Full_Name) – Charindex(' ', Full_Name))) as [Last Name]
This takes the space position of 5 in the first example, adds 1 to remove the space itself, and returns the remaining characters by evaluating how many characters remain
Here is the full SQL code that you should have if you have been following along
select Full_Name
, Charindex(‘ ‘, Full_Name) as [Space Position]
, len(Full_Name) as [Name Length]
, left(Full_Name,(Charindex(‘ ‘, Full_Name)-1)) as [First Name]
, substring(Full_Name,(Charindex(‘ ‘, Full_Name)+1), (len(Full_Name) -Charindex(‘ ‘, Full_Name))) as [Last Name]
from Names_Test
You can apply this same technique to split other types of data, or substitute the space for a different character
Subscribe to our channel to see more tips and timesavers
Select Distinct YouTube Channel
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.
Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
