Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Split Function Example in 2008 to Split Comma Separated String into Table

Jul 28, 2013
Introduction

Here I will explain simple split function in SQL Server to split comma separated string into table values in SQL Server database or How to split comma separated string with custom split() function in SQL Server

Description:


To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below

Custom Split function to split comma separated string into table


CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
--     ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
Once we create custom function Split() run sample query like as shown below


SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',') 
Once we run above query we will get output like as shown below

Output


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

13 comments :

Anonymous said...

Nice work
But How to add Those Numbers By comma seperated

Anonymous said...

I have a checkboxlist for and based on the selected items in the first checkboxlist I want to display cities. For example if user has selected U.P. n Haryana then cities of both states should be displayed on city checkboxlist.. Please reply as soon as possible.
warm regards

Anonymous said...

very helpfull..Thank u

Anonymous said...

very helpful...

Unknown said...

hi... i have 10 items in my form each item have 10 checkbox. now i checked the checbox the values are stored in database table like this 1,2,3.then i want the checked box are disabled after value stored tothe particular ids
what to do ???? helpme....

Unknown said...

See This one so simple I hope you would like this logic

alter function CustomSplit(@String nvarchar(max),@dilmiter nvarchar(1))
RETURNS @Results TABLE (Items nvarchar(4000))
begin


declare @index1 int
declare @temp1 nvarchar(max)
while CHARINDEX(@dilmiter,@String)<>0
begin
select @index1=CHARINDEX(@dilmiter,@String )
select @temp1=SUBSTRING(@String ,1,@index1-1)
--select @temp1

insert into @Results values(@temp1)


set @String=REPLACE(@String,@temp1+@dilmiter,'')

if(CHARINDEX(@dilmiter,@String)=0)
begin
insert into @Results values(@String)
end
end
return
end



select * from dbo.CustomSplit('111,112,122,133,126',',')

Unknown said...

HI, you don't by any change have a function to split a column in a table with multiple entries (delimited) into multiple columns. The data looks something like this:
(10:00~11:00~12:00). Needs to be dynamic as values can be from 1 to max 30 .
Regards

Help said...

I have a table like this

Id FullName
-------------------
1 Cleo,Smith,james

I want to separate the comma delimited string into two columns

Id FullName Name Surname Last
--- ------------ ------- ----------- ------
1 Cleo,Smith Cleo Smith james

How can i do this?
Give me query for this?

Anonymous said...

nice

Anonymous said...

thx budy its works for me

Unknown said...

how to seperate multiple varibale at the same time

Anonymous said...

nice

Unknown said...

How we can split string into column wise without using pivot. I have code with pivot, but its taking longer time for larger records till (1 lac). Can u give me some solution.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.