How To Split Comma Separated Values In SQL Server

This article explains how to split comma separated values by using Tally table.
  • 3994

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:

CommaSeperatedValues.jpg

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.