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

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

You may be interestind in this

Split a column in BigQuery – Select Distinct Limited

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