Split a Column in SQL

Have you ever needed to be able to split a column in SQL?

This simple example walks you through the steps involved using a column with a First and Last name and shows you how to split these into two separate columns

Here is the final SQL code

select name 

, Charindex(' ', name) as [Space Position] , len(name) as [Name Length] 

, left(name,(Charindex(' ', name)-1)) as [First Name] 

, substring(name,(Charindex(' ', name)+1), (len(name) -Charindex(' ', name))) as [Last Name] 

from [dbo].[names] 

and here is the SQL to add a sample table of names

create table names (Name varchar(100)) 

insert names select 'Ivan Yu' 

insert names select 'Kenya Villarreal' 

insert names select 'Simeon Hancock' 

insert names select 'Mercedes Vaughan' 

insert names select 'Frank Smith' 

insert names select 'Lee Mooney'