Split Function in Sql Server to break Comma-Separated Strings into Table

Sql Server does not (on my knowledge) have in-build Split function.
Split function in general on all platforms would have comma-separated string value to be split into individual strings.
In sql server, the main objective or necessary of the Split function is to convert a comma-separated string value (‘abc,cde,fgh’) into a temp table with each string as rows.

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
	declare @idx int     
	declare @slice varchar(8000)     
    
	select @idx = 1     
		if len(@String)<1 or @String is null  return     
    
	while @idx!= 0     
	begin     
		set @idx = charindex(@Delimiter,@String)     
		if @idx!=0     
			set @slice = left(@String,@idx - 1)     
		else     
			set @slice = @String     
		
		if(len(@slice)>0)
			insert into @temptable(Items) values(@slice)     

		set @String = right(@String,len(@String) - @idx)     
		if len(@String) = 0 break     
	end 
return     
end

split function can be Used as

select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

would return

Above Split function can be used to pass parameter to IN clause in sql server.

| | More

Comments

juan pablo says:
Jun 12, 09
Thanks for the function man, works like a charm.
srinivas says:
Jun 29, 09
thanks very muck it was very handy
ratna says:
Jul 07, 09
I am using your Split function. It works great. Can you tell me, How to store those splitted values into the database table. I am using insert statement. It is giving error. May be I am using in a wrong way. Can you please tell me how to insert them into the table?
logiclabz says:
Jul 08, 09
ratna,
create table #temptable (id int identity(1,1),col1 varchar(255))

insert into #temptable
select * from dbo.split('Chennai,Bangalore,Mumbai',',')

select * from #temptable
DDBA says:
Jul 21, 09
Thanks for this inspiring code. I have modified it to also be used as a scalar function. Tried to submit the modified code but your website does not allow its submission. Will be glad to submit the modified version if requested.
Riya says:
Aug 06, 09
Hey have been looking for such a thing for a long time now, but may i use this on an already exiting table, where most the coloum are with ; and , seperated data.... Can you help please
Shree says:
Sep 17, 09
Great function and it helped very well. But what if the input string length exceeds 8000?
qwe says:
Oct 22, 09
whn i execute Select dbo.Split('1,2,3,4',',') it give me following error:: Msg 4121, Level 16, State 1, Line 2 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous. y so?
Ashok Kumar says:
Nov 04, 09
Working fine and very help where we generally use in option in procedure. we can use this function... thanks lot.
Jason Au says:
Nov 07, 09
As stated by Riya, I have a table containing delimited data that we need to split into multiple rows. One way is to create a cursor and loop thru all rows. Anyone know of a more efficient method?
Vicky says:
Nov 20, 09
Thank you-- saved me a lot of time on an already lengthy task!
real quick... says:
Nov 23, 09
i have a table, and there is a column which has a string value asdfadf,zcvzcv how do i run this function against the table column? something like this? select * from [mydatabase].[dbo].[split] (mycolumn, ',') just wondering.
Shaun says:
Dec 04, 09
Hey, thanks. Works like a chard
nikhil says:
Dec 11, 09
thanks........
mani says:
Dec 26, 09
Great function and it helped very well
Jim says:
Dec 28, 09
If the last character in the string is a space, it fails to parse correctly. DECLARE @String SET @String = 'IMM,CE ,CM ' SELECT * FROM dbo.split(@string,',') RESULTS: IMM E M
Jim says:
Dec 28, 09
Changing the LEN function to DATALENGH should correct the problem with trailing spaces.
Eric Mott says:
Dec 30, 09
This just saved my life. Thanks!
somasekhar says:
Jan 06, 10
hai Thank u very much
Khushboo says:
Jan 15, 10
Hi, I want to apply this function to one of my table in the database, how can I do that? e.g If in a search box, I put keywords like "sql,java" then it should give those results which have either "sql" or "java" or both, in the skill sets. "Skill set" is a column name in the table. Please resolve this problem.. Thanks.
chithu says:
Jan 19, 10
u r great man.i like it
Rajeev says:
Jan 21, 10
Great and simple example . It works good. Complete solution for the task.
Deepu says:
Feb 09, 10
How we can achieve the same without using that function(I need to use only SQL query). Thanks in advace.
harini says:
Mar 19, 10
There are two tables "germanytable2" and "thridfile". following are the columns each table has : "germanytable2": bpcustname,countryname,groupid etc. "thridfile": tradingname,country,id etc. task: get all those id's from "thirdfile" if the condition tradingname=bpcustname. logic i thought of: first will do the exact match like: //select a.bpcustname,a.groupid,a.countryname,b.tradingname,b.id from germanytable2 as a join thirdfile as b on a.bpcustname = b.tradingname// **there are 10334 matches. now i want to put these matches aside and compare from those which are notmatched with soundex function.** if i compare directly wid soundex its giving me enormous results..so jus want to compare from the remaining. here logic : create a table wid columns like groupid,bpcustname,country,match from both the old tables "germanytable2" and "thirdfile" code: select bpcustname,groupid,countryname into germany_newtable from germanytable2 select id,tradingname,country,match into thirdfile_newfile from thirdfile will go to design part and add other columns called "match" for both the newly created tables. then the code goes as follows: select a.groupid, a.bpcustname, a.countryname, a.match, b.tradingname, b.id, b.country, case when a.bpcustname = b.tradingname then 'y' END 'Category' from germany_newtable as a join thirdfile_newfile as b on a.bpcustname = b.tradingname. now i want to update the match column to 'y' and perform the same using soundex function for the remaining data using the conditions soundex(a.bpcustname) = soundex(b.tradingname) and match != y. hope it is little clear and need some help in this regard.
Durga Prasad says:
Mar 31, 10
I Have Three Strings like 'New Inc., Family Academy' 'ABC Company' 'XYZ Organization' how to use the above function to make use of it?
Saravanan says:
Apr 01, 10
Thanks.But how to split a string using different operators.(ex: A+B-C/D) Is it possible to split '+,-,/' wise.
RSA Online says:
Apr 29, 10
Thank you, this split function was exactly what I needed. Can't believe that something like this is not built-in.
Shahanaj says:
May 04, 10
Hi Thank u very much,This good,but I have a table containing delimited data that we need to split into multiple rows. And Use this function.please tell me how ca i do that
Mani says:
May 07, 10
declare @str varchar(100) set @str=( select * from details) insert into #temptable select * from split(@str,',') select * from #temptable I get only the first value. Why? Can any one help? details table has one record '111,222,333,444,555'
Natarajan says:
May 14, 10
Very useful function. Thanks.
Tanweer Hussain says:
May 20, 10
I am trying to write a fucntion to do this. However havign no solution yet after 4 hour of work, I am trying to split a String into Row and Column "A_B_C,D_E_F" Into A B C D E F Thanks
Praveen says:
Jun 04, 10
Check this out for the same: http://praveenbattula.blogspot.com/2010/06/best-split-udf-function-with-delimeter.html
annamalai says:
Jun 15, 10
thank u so much..it has helped me a lot
shov says:
Jul 16, 10
Great function! You have helped out a lot of people, keep up the good work!

Do you like this post?

Split Function in Sql Server to break Comma-Separated Strings into Table

Comments

Related Posts

Leave a reply

Categories

Tools

Recent Posts

Blogroll