How To Split Comma Separated Values In SQL Server
This article explains how to split comma separated values by using Tally table.
In this article I describe how to split comma separated values by using Tally table. We will implement this using Tally table.
Create Tally Table
set nocount on;
if object_id('dbo.Tally') is not null drop table dbo.tally
go
select top 100 identity(int,1,1) as ID
into dbo.Tally from master.dbo.SysColumns
alter table dbo.Tally
add constraint PK_ID primary key clustered(ID)
go
select * from dbo.Tally
Split Comma Seperated Values
DECLARE @String VARCHAR(1000), @Delimiter CHAR(1) = ','
SET @String = 'John,Tim,Albert,Peter,Rick'
SET @String = @Delimiter + @String + @Delimiter
SELECT SUBSTRING(@String, ID+1, CHARINDEX(@Delimiter, @String, ID+1) - ID-1) Splited_Values
FROM dbo.Tally
WHERE ID < LEN(@String)
AND SUBSTRING(@String, ID, 1) = @Delimiter
Output:
