Select Distinct Logo Clear Background

Business Analytics Blog

How to split a column in SQL Server

How to split a column in SQL Server

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

A list of full names, with first name and last name separated by a space

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

list of full names and a column showing the 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

list of names showing space position and length of the name

 

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

 

a list of full names and a column showing the first name after being split out from the full name

 

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

a list of names after using SQL to Split a column into multiple columns

 

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!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert