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

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!

• Bharat says:
  Sep 13, 10

  Thanks for this stuff.

• Nitin K says:
  Sep 14, 10

  This function helped me lots. Thanks.

• Angel says:
  Oct 07, 10

  Awsome, It really helps. Thanks for sharing! Blessings to you!

• tabtim says:
  Oct 21, 10

  Thank you so much Really help me

• Ryan Sardon says:
  Oct 23, 10

  Great code snippet!! Thanks for sharing.

• Raja says:
  Oct 25, 10

  I have a table like below and I wan to seperate the comma values and get distinct values from them slno WFTools ----- ----------- 1 EMEA-OVSC, OVSD 2 EMEA-OVSC, Radix 3 Radix 4 OVSD, Dav, MR and I want EMEA-OVSC Radix OVSD Dav MR can some one help on this?

• Elavarasu says:
  Oct 28, 10

  Thank u.............................

• Softintellect Network says:
  Oct 29, 10

  This is a very good code snippet. Mant thanks for sharing. It works like a charm

• speedbit says:
  Nov 04, 10

  Adress Chennai,Tamil Nadu,India i need output as below City State Country > These are Meta Data Chennai Tamil Nadu India This is data

• Mahendiran.M says:
  Nov 15, 10

  select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',') The above is not right... Try the following,I hope that It may help you. select * from [dbo].[UDF_SPLIT]('Chennai,Bangalore,Mumbai',',')

• Vanathi says:
  Nov 24, 10

  Thanks a lot!!!!!!!!!! This function is really so helpful for me :)

• Cris says:
  Dec 14, 10

  Great function! But i have a problem, i m using sql server 2000, when string length exceeds 4000 it retrieve an error: Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query. Any ideias?

• experts comment says:
  Dec 19, 10

  good one... http://www.a2zmenu.com/MySql/SQL-Split-Function.aspx

• Juliana says:
  Jan 06, 11

  Thanks! It worked really well!

• Francis says:
  Jan 06, 11

  Thanks alot. The function works very well. Cheers

• Ramki says:
  Jan 10, 11

  select * from dbo.SPLIT(@donationtype,'~'),dbo.SPLIT(@amount,'~') i want to use multiple split fucntion in select statement please help me

• Amit says:
  Jan 11, 11

  Thanks...it worked in a single shot!!

• Ranajit Bhowmick says:
  Feb 14, 11

  Done a mavelous job.

• zain says:
  Mar 07, 11

  How do I store return string values in different columns including null values, example, String: ABC,DEF,,,DGG,SS,,, colum1 column2 column3 etc...

• Jack Ad. says:
  Apr 02, 11

  Such a perfect piece of sql! Used in a complex query to give users ability to search a dateTime field by year, month/year, full date combination, and your function along with a scroll cursor worked just beautifully.

• Mike says:
  Apr 07, 11

  You just saved my butt... thanks so much!

• mohammad says:
  Apr 07, 11

  thanks

• Valdemar_Rudolfovich says:
  Apr 25, 11

  Thanks a lot! This is what i search for!

• Guy says:
  May 02, 11

  Brilliant function, many thanks indeed, helped me a great deal.

• chamila says:
  May 05, 11

  check this also: http://chamilaw.blogspot.com/2011/05/concatenation-in-select-and-split.html

• Jeet says:
  May 07, 11

  I need to convert this MS SQL Function into My SQL. n How to use it. I will be great full is someone helps me pls. Its URGENT.

• Neeraj Mehta says:
  May 13, 11

  Thanks a lot. You made my day. I have book marked this site.

• Eugene says:
  May 14, 11

  Ggreat example. Thank you for your post

• Elena says:
  May 19, 11

  Thanks. it was help

• RAJ SATRE says:
  May 19, 11

  SELECT PARSENAME(REPLACE('Hello RAJ SATRE', ' ', '.'), 3) --return Hello

• Jack Freudenheim says:
  May 20, 11

  Big help, thank you!

• Blueprint RSA says:
  May 26, 11

  Thank you for this very informative blog. The function is very simple and has helped me BIG TIME. Will bookmarked this site and wait for updates.

• mostafa says:
  Jun 01, 11

  thank you! very much

• sanam says:
  Jun 14, 11

  Nice.. Its working perfect

• Prajwal says:
  Jun 15, 11

  Thanks it works great :-)

• Ravi Der says:
  Jun 20, 11

  thnx frnd....

• jagadish says:
  Jun 23, 11

  sir u have given me this snippet sir its useful but i want it to be used in a existing table i dono how to use it sir kindly guide me sir thanking u

• Asfak says:
  Jun 24, 11

  Cool Man.... Very Good Programming.....

• ryman says:
  Jun 24, 11

  how can i conver this function to display strings in sperate columns . like converting string(name1, name, name3) into seperate columns name1 name2 name3

• tejas says:
  Jun 27, 11

  Thanks a lot for sharing :) God bless you

• jac27 says:
  Jun 30, 11

  Muchas gracias por compartir!!

• Amy says:
  Jul 28, 11

  Thanks for sharing! but select top 10 * from dbo.split(N'5,4,8, 9 ',',') didn't return the right result

• mazhar says:
  Jul 30, 11

  wow just what i need

• ganesan says:
  Aug 17, 11

  Excellent function. Very useful

• Vidhya says:
  Aug 18, 11

  How to call this split function in stored procedure for store multiple data in table ?

• cheese says:
  Aug 22, 11

  Thank you, I have been looking for a function to do this Instead of using CURSOR and FETCH to manually and tediously read every comma in the account number , I used this function. Thank you very much...

• deepika says:
  Aug 24, 11

  Can U please write or convert the same Split function in MYSQL.

• Vadivel says:
  Oct 02, 11

  Hope I am not very late to the party :) Just thought of posting the link of my blog post relating to this topic. http://vadivel.blogspot.com/2011/10/how-to-split-delimited-string-values-in.html Hope it would be of help to somebody someday :)

• Rinki Gupta says:
  Oct 05, 11

  I am using the above written query, but not getting the desired result.Plss help me

• RodSF4 says:
  Nov 18, 11

  Thanks a lot for this wonderful function! Really helped me solve my problem...

• Tsawm says:
  Nov 19, 11

  Thank you very much.. I had headache for that... now I got it.. declare @StringValue varchar(50) set @StringValue = (select authors from tbl_book where book_id='B0002' ) select top 10 * from dbo.split(@StringValue,',')

• Erick says:
  Nov 21, 11

  Muchas gracias por tu aporte, thanks!!!

• Praveen says:
  Dec 05, 11

  Thanks a lots! .Really superb function.

• KISHAN CHOUDHARY says:
  Dec 07, 11

  I wani to use this function in table i am using select * from dbo.split(select cuisines from restaurant,',') but its not working Here cuisines column contains comma separated values. How i can get this??? Please Help!!

• KISHAN CHOUDHARY says:
  Dec 07, 11

  I got my solution select b.items as splitvalue from restaurants_details a cross apply dbo.fun_Split(a.cuisines,',') b

• Gerrysoft says:
  Dec 19, 11

  Thank you ver much. Your code is a very important contribution for the programmers

• Saeed Neamati says:
  Dec 22, 11

  That was helpful, thanks. I specially liked the idea of delimiter. This way, your function is not an ad-hoc CSV separator, and can turn any delimitted-string into an array of tokens. Great.

• Chris says:
  Dec 29, 11

  Thank you for the function. I'm fairly new to SQL and this save me a bunch of time. It allowed me to concentrate on the core of my project :)

• Pepe says:
  Jan 02, 12

  Great !! this function is usefull, as i was searchng Thanks bro !!

• vnreddy says:
  Jan 12, 12

  Hi all i want to "con cat" all columns in a single line please use procedure with "loop "

• Nathan says:
  Jan 18, 12

  Thank you very much for sharing this awesone function.

• Mani says:
  Jan 25, 12

  Thanks a lot

• Ray says:
  Jan 26, 12

  Thank you for this handy function. Saved me a few hours. It works great!

• tester says:
  Jan 27, 12

  CHANGE: insert into @temptable(Items) values(@slice) TO : insert into @temptable(Items) values(LTRIM(RTRIM(@slice))) To get rid of spaces if you do: A, B, C instead of A,B,C Thanks,

• Vincent says:
  Feb 08, 12

  Thank you so much! Heaven Sent!

• manel says:
  Feb 10, 12

  Thank you very much, I'm a new employee in a developpent socity and your function is a big solution for a big problem that I have recently. Thanks

• Muhammad Imran says:
  Feb 14, 12

  Hi, Nice & very helpful article, I recently did the development of the same function via XQuery. http://raresql.com/2012/02/14/split-function-in-sql-server/ Imran

• Hemant says:
  Feb 16, 12

  Works great! Really helped me. MS SQL should make this as inbuild funtion...

• guru rajan says:
  Mar 14, 12

  superb query. Main thing is here nothing is hardcoded. Keep it up

• Eduardo P says:
  Mar 17, 12

  Great code, saved me a lot of work. Thanks a lot!

• Hina says:
  Mar 20, 12

  Excellent utility

• Mai says:
  Apr 03, 12

  Thanks a lot!!!!

• Tim G says:
  Apr 19, 12

  Thanks for sharing!

• super~ says:
  May 03, 12

  YEAH BABY !~~~~ THANKS!