By David Wiseman (Administrator)Created 13 Dec 2009
My Rating:
Vote
Rating:
Not Rated
Views:23360
Downloads:34
Source:

Split String Function (CTE Version)

Language:  T-SQL

Compatibility

SQL Server 7 No SQL Server 2000 No
SQL Server 2005 Yes SQL Server 2008 Yes
Description

Function to turn a comma separated (or other delimiter) list into a table.


Notes
This function uses a recursive CTE (Common Table Expression) to parse a delimited string into a table.  Here is a simple example of how the function can be used:
  1. DECLARE @str nvarchar(max)   
  2. SET @str = 'abcde,fghij,klmno,pqrst,uvwxy,z'  
  3. SELECT value    
  4. FROM dbo.fnSplitString(@str,',')  
The first parameter is the string that you want to parse, the second is the delimiter that you want to use to split the string (normally a comma or a semi-colon). 
 
Recursive CTE's are limited to 100 levels of recursion by default.  As soon as a string is split into more than 101 parts, you will get an error stating that this limit has been exceeded.  You can see this by running the example below:
  1. DECLARE @str nvarchar(max)   
  2. SET @str = STUFF(REPLICATE(',abcde',102),1,1,'')   
  3. SELECT value    
  4. FROM dbo.fnSplitString(@str,',')  
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
 
 If you change the value "102" to "101" the statement will work without any problems.  To bypass the limit of 100 levels of recursion, you need to add a query hint to the code - OPTION(MAXRECURSION 0):
  1. DECLARE @str nvarchar(max)   
  2. SET @str = STUFF(REPLICATE(',abcde',102),1,1,'')   
  3. SELECT value    
  4. FROM dbo.fnSplitString(@str,',')   
  5. OPTION(MAXRECURSION 0)  
The code above will now complete without any problems.

Alternative Methods

Splitting strings is quite a common requirement and there are a number of different solutions available.  I think the CTE method I've posted is a very neat and efficient solution, but it does have it's shortcomings.  As CTE's were first introduced in SQL Server 2005, this solution is not compatible with earlier versions of SQL Server.  Also, you will need to use the MAXRECURSION query hint with this function to allow it to return more than 101 rows.  Unfortunately it isn't possible to incorporate this query hint into the function, so you will need to use the query hint everywhere you use the function.  I've also posted a different method that uses a WHILE loop to parse the string which is compatible with earlier versions of SQL Server:
 
Split String Function (WHILE loop method)
 
The CTE version does seem to outperform the WHILE loop method, but the WHILE loop method is compatible with earlier versions of SQL Server and doesn't require a query hint to be used.
 
A number of other solutions have also been posted in this article which is definitely worth a read. I've found my own CTE function to be faster than the string parsing methods mentioned in the article - even for larger strings, but as always it's best to conduct your own performance analysis.
 
Code

Line Numbers: On  Off      Plain Text
CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
	WITH a AS(
		SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
		UNION ALL
		SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
		FROM a
		WHERE idx2>0
	)
	SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
	FROM a

 


Got a useful script? Click here to upload!


 

  Post Comment
Order By:  
User Comments
      

Posted On: 9/28/2018 7:29:57 AM

jinyi928 pandora charms moncler outlet ugg boots outlet longchamp basketballl shoes adidas shoes bottega veneta mbt shoes supreme clothing coach outlet lacoste polo coach outlet christmas presents off white jordan adidas nmd longchamp outlet canada goose outlet canada goose outlet louboutin shoes canadian goose clarks outlet michael kors outlet skechers shoes new balance shoes canada goose moncler outlet coach outlet true religion salomon shoes off white shoes clarks outlet james harden shoes ray ban sunglasses ralph lauren polo pandora champion sportswear tory burch outlet coach outlet kate spade outlet michael kors outlet mlb jerseys nike tn ralph lauren polo oakley sunglasses true religion jeans champion clothing christian louboutin outlet christian louboutin shoes adidas superstar clarks shoes ralph lauren polo red bottom shoes pandora jewelry ralph lauren uk red bottom shoes moncler outlet canada goose jackets coach outlet coach outlet online canada goose outlet coach outlet converse trainer ecco shoes nike outlet superdry uk coach outlet online ugg outlet clearance canada goose outlet kate spade handbags adidas yeezy coach outlet jordan uk canada goose jacket hermes belt hogan shoes jordans ray ban sunglasses supreme ray ban sunglasses ray ban sunglasses true religion jeans outlet north face coach handbags ugg boots off white clothing canada goose ralph lauren polo christian louboutin shoes ralph lauren uk canada goose clothing coach outlet ugg outlet boots coach factory outlet air max 90 christian louboutin outlet christian louboutin off white outlet ugg outlet jordan shoes pandora charms asics shoes nike shoes outlet christian louboutin outlet moncler jackets ed hardy uk fitflops clearance ugg boots christian louboutin outlet michael kors outlet canada goose outlet pandora outlet tod's outlet manolo blahnik canada goose outlet red bottom shoes nike shoes salomon shoes nike blazers coach outlet dsquared2 ralph lauren fitflops clearance north face true religion jeans canada goose jackets pandora jewelry true religion jeans outlet jordans louboutin shoes asics shoes coach outlet nhl jersey jordan clothing yeezy boots 350 nike shoes nike shoes coach outlet online nike outlet factory jordan 8 fitflops clearance ralph lauren polo jordan shoes canada goose outlet jordan 4 adidas yeezy giuseppe zanotti kate spade outlet true religion canada goose nike clothing louboutin shoes nike air max mbt shoes issey miyake ferragamo belt air max 90 ultra boost 3.0 birken stock moncler outlet ugg boots canada goose outlet moncler outlet moncler jackets hogan shoes coach factory outlet louboutin shoes stussy ugg boots clearance true religion jeans soccer shoes pandora outlet pandora charms ugg boots ecco shoes canada goose outlet kate spade outlet jordan shoes nba jerseys reebok outlet adidas clothing louboutin shoes michael kors outlet fitflops clearance adidas clothing vibram five fingers coach outlet ray ban sunglasses nike shoes fidget spinner ralph lauren polo christian louboutin pandora jewelry coach outlet online pandora charms clarks shoes christian louboutin coach outlet coach outlet factory coach outlet kate spade outlet moncler jacket ugg outlet canada goose outlet mbt shoes ugg outlet ferragamo shoes valentino shoes red bottom shoes christian louboutin shoes pandora outlet prada handbags ralph lauren polo moncler outlet coach factory outlet coach outlet christian louboutin shoes kate spade outlet michael kors outlet ralph lauren polo longchamp outlet ralph lauren uk hermes belt coach outlet adidas outlet hugo boss kate spade nike shoes canada goose outlet hugo boss louboutin shoes jordan shoes prada shoes ugg boots clearance christian louboutin outlet supreme clothing ugg outlet air max 90 dsquared2 christian louboutin outlet christian louboutin nike huarache coach factory outlet

valentino11
Posted On: 10/1/2018 8:35:15 AM

People often say: "Opening UGG Australia a book is useful, reading is good." However, careful scrutiny is not entirely correct.Kate Spade Outlet Store If we are interested in every book, then "opening up the Cheap UGG Boots benefits" may not be beneficial. Nowadays, my classmates may like to watch martial arts,Coach Outlet romance-like novels, books and periodicals, and sometimes they will reach the level of sleepless nights and forgetting their hands. Once they read the book, they will go into flames.UGG Slippers Then they only want to study books in class, and they don’t have the heart to learn. The results will plummet.Cheap Nike Air Max 2018 Still others are attracted to some of the plots in the book, imitating the characters in the book, and sometimes going to the path of crime. Isn't this a book that hurts yourself?Adidas Originals Superstar This is just the first layer included in the "opening is not necessarily beneficial":Victoria Secret Outlet unwinding is not necessarily beneficial. There is also a second floor. The second is that we have to Cheap Kate Spade Handbag choose books. Don't look at books that are unhealthy and don't help us much. To read a book, look at books that are good for our physical and mental health, for our study and for our lives.Nike Air Max Tn Such a book is a good book. If you look at a book well, you will not ruin yourself. Some people compare books to friends,Nike Air Max optimistic about books, that is, making good friends, will progress; then not good books,Fila Outlet of course, bad friends are friends, then you will regress. Because the near Zhu is red, the near ink is black!Victoria Secret Outlet Therefore, only looking at the book will be good for you. Therefore, my point is that opening a roll may not be beneficial.Victoria Secret Pink I sincerely hope that every student can have a choice to read.Fila Sandals And don't ruin your life because you misread a book and lead yourself astray. Since the Song Taizong Zhao Guangyi said it, it has been more than Adidas Superstar a thousand years since its inception. For thousands of years, it has been accepted by countless people, and it has been passed down by word of mouth, and it has been regarded as a famous saying.Coach Outlet Store Online Parents often regard children,Cheap Nike Shoes elders and younger generations. It is necessary to teach or urge them to Nike Air Vapormax"read a good book" and think that as long as they study, they will gain knowledge and benefit.Balenciaga Sneakers Therefore, people often say: "The book has its own thousand millet", "the book has its own gold house", "in the book Own Yan Ruyu. In fact, whether the opening of the book is beneficial Coach Outlet Sale depends on whether the book is good or bad. The book is a good book, and it is beneficial to open the book; and if the book is not UGG Boots Sale a good book but a bad book, opening the book is not only useless but harmful. In recent years, there have been many good books in our book market, but there have been many bad books.Coach Factory Outlet You go to the bookstores of big and small bookstores to go and see, you can find a few bad books everywhere. What is the "flatter", what is "damaged people",Kate Spade Outlet what "face learning", and so on, this kind of book that teaches people to learn bad, only poison and corrosive to young readers, without any benefit.UGG Outlet As for books that promote various negative decadence, cynicism, and philosophy of life,Air Max Tn Plus books that express a yellow and decaying lifestyle are even more harmful. The vast majority of people who talk about the Coach Outlet Online benefits of opening a book are out of kind. They are good at opening a book, and the intention is to read a good book. However, the current world is very complicated,Coach Factory and there are certain people who, for some purpose, use bad books as good books to sell to the world,Kate Spade Outlet especially young people. They also said that opening a book is beneficial,Coach Outlet Online but it is actually harmful to readers. Most of the Victoria Secret Clothing current young people have strong self-motivation and curiosity,Coach Factory Outlet Store and they are fond of reading, but they often lack discrimination. I think that our teachers have the responsibility to UGG Outlet Store remind them that they must read more books, at least read harmless books, and never read bad books.Balenciaga Shoes Unwinding may not be beneficial!

xiaoou
Posted On: 11/13/2018 3:18:25 AM

michael kors uk burberry ugg boots germany world cup jersey michael kors outlet true religion outlet jordan adidas zx flux ralph lauren outlet nike outlet lebron 11 armani handbags timberland pas cher jordan 6 ray ban sunglasses sac michael kors dyson hair dryer burberry outlet dolce & gabbana stuart weitzman belgium world cup jerseys montres lunette ray ban ferragamo cheap uggs wedding dress michael kors canada ray ban occhiali dsquared2 jeans 10 deep clothing harry winston jewelry new balance soldes oakley pas cher toms shoes prada tory burch golden goose sneakers air max 2018 iphone cases kate spade outlet nike air max beats headphones jordan 12 ralph lauren jordan 6 boy london clothing sac longchamp converse uk nike air max mizuno kevin durant shoes givenchy handbags michael kors outlet bottega veneta sunglasses nike store abercrombie tory burch outlet curry 2 replica watches mac makeup prada outlet ray ban sunglasses keen sandals adidas flip-flops montre homme pandora jewelry jimmy choo sunglasses bcbg max azria louboutin outlet gucci outlet moncler outlet iphone x case prada sunglasses manolo blahnik shoes dsquared2 outlet air more uptempo adidas shoes bally shoes g-star jeans adidas trainers uk ray ban vans outlet ed hardy jordan pas cher soccer jerseys basketball jersey hermes birkin fossil handbags guess canada michael kors kobe 10 nike air max curry 4 hollister outlet lebron shoes longchamp handbags nike free burberry outlet alife clothing nike air max nike outlet nike huarache nike blazer chanel handbags nike foamposite arcteryx jackets roshe run michael jordan asics gel barbour jackets timberland boots air jordan cartier sunglasses ugg outlet vans scarpe beats headphones toms outlet karen millen beats by dre jordan 13 spain world cup jersey michael kors uk carson wentz jersey oakley uk nike air zoom pegasus barbour isabel marant shoes guess nike air max 2017 pandora charms nike air force 1 jordan 31 air max swarovski gioielli oakley sunglasses air jordan polo ralph lauren jordan 5 sac vanessa bruno furla handbags adidas outlet store basketball shoes prom dresses new balance shoes nfl jerseys balmain jeans occhiali oakley adidas outlet store scarpe hogan christian louboutin christian louboutin shoes ghd flat iron jordan 7 nike roshe run true religion jeans chopard jewelry mac makeup jordan 9 dolce & gabbana coach outlet michael kors outlet lululemon outlet softball bats ralph lauren outlet supra shoes jordan clothing swarovski jewelry bulgari jewelry replica watches longchamp ray ban gucci handbags adidas soccer cleats hogan nike blazer reebok shoes replica watches portugal world cup jersey mac cosmetics jordan 14 insanity oakley glasses carrera sunglasses chanel outlet curry 3 burberry sunglasses pandora charms cheap wedding dresses valentino ray ban sunglasses nike outlet kobe 9 elite versace handbags uggs outlet nike roshe run nike kyrie 2 michael jordan christian louboutin diesel jeans ugg boots huf clothing calvin klein outlet cheap wedding dresses nike mercurial nike outlet online insanity workout basket nike oakley vault adidas outlet store ghd celine handbags burberry sale asics trainers moncler jackets gentle monster sunglasses england world cup jersey jordan 8 adidas running shoes vans outlet clarisonic mia longchamp pas cher jordan 32 ferragamo sunglasses uggs outlet karen millen air force 1 pandora jewelry yeezy shoes nike air max 90 polo ralph lauren polo ralph lauren nike roshe run fitflops alexander mcqueen handbags christian louboutin pas cher mizuno ferragamo marc jacobs handbags seahawks jerseys mont blanc pens lacoste jeans nfl jerseys kd 10 nike pas cher balenciaga sneakers montre pas cher jimmy choo shoes nike free run polo ralph lauren canada goose jackets nike roshe france world cup jersey tn pas cher cheap jordans tory burch burberry kyrie irving jersey ugg jimmy choo converse kevin durant shoes chloe sunglasses converse outlet store burberry clothing tommy hilfiger pas cher prada handbags lebron shoes longchamp outlet longchamp outlet bathing ape new balance trainer kate spade uk nike air max 90 coach handbags uggs outlet coach factory outlet ai max vibram fivefingers goyard handbags guess factory fendi rolex watches polo ralph lauren argentina world cup jersey flip flops tommy hilfiger ugg boots hermes aaron rodgers jersey adidas superstar salomon shoes jordan 11 new balance nike free cheap jordan shoes new balance femme hermes bags nike blazer adidas superstar michael kors handbags kate spade kd 9 sac michael kors air max 90 omega watches nike air max 2017 true religion jordan nike free run gucci belt calvin klein jeans eagles jersey asics gel orologi rolex skechers sneakers freshjive clothing fendi handbags insanity workout polo lacoste pas cher jordan 10 air max 90 adidas soccer chloe handbags michael kors outlet nike shoes pandora uk nike vapor max links of london uk miu miu shoes nike uk hermes jewelry swarovski crystal chrome hearts jewelry supra shoes air max plus true religion jeans furla handbags christian louboutin nike shoes burberry handbags canada goose jackets nike roshe designer sunglasses james harden jersey champion clothing basket store ray ban sunglasses louboutin pas cher nike mercurial superfly adidas crazy kevin durant jersey bcbg dresses rolex watches guess handbags adidas outlet store kobe bryant shoes coach purse pandora bracciali michael kors nike free run air force one lacoste shoes longchamp handbags nike air max dolce & gabbana dansko outlet saint laurent handbags vans pas cher brazil world cup jersey ghd hair straighteners marc jacobs handbags clarks shoes air max 90 nike air nike air max michael jordan parajumpers converse shoes gucci shoes christian louboutin oakley sunglasses reebok outlet jimmy choo dansko shoes true religion outlet oakley sunglasses lunette oakley kates pade outlet online ferragamo shoes bijoux swarovski timberland boots jordan 4 adidas slides curry 5 ray ban sunglasses christian louboutin air max 95 bottega veneta nike roshe jordan ray ban pas cher coach outlet ferragamo shoes chi flat iron jordan xx9 cheap oakley sunglasses kyrie 4 jordan shoes bape clothing armani sunglasses bose headphones fendi sunglasses nike air max kobe 11 hermes bags kids jordans dolce & gabbana sunglasses red bottom shoes hollister bvlgari sunglasses jordan 2 true religion tods shoes coach outlet online pandora bijoux fitflops outlet hogan shoes michael kors handbags nike free cheap jerseys abercrombie and fitch moncler jackets michael kors armani jeans babyliss pro nano titanium softball bats nike canada oakley sunglasses giannis antetokounmpo jersey jordan 1 adidas outlet abercrombie cheap oakley sunglasses nike air force nike shoes giuseppe zanotti outlet mizuno running shoes marc jacobs handbags kristaps porzingis jersey nike factory air max adidas clothing converse all stars jordan 3 nike air max 97 chaussure nike ferragamo belt nike air max 95 nike outlet store chanel outlet kobe 12 jbl headphones givenchy jewelry coach outlet michael kors outlet online salvatore ferragamo cheap jerseys jordan puma shoes cartier bracelet tommy hilfiger nike clothing mont blanc pens converse chuck taylor abercrombie herve leger dresses converse kenzo clothing stuart weitzman shoes nike mercurial tiffany jewelry timberland boots dak prescott jersey jordans ai max ralph lauren beats pill gucci outlet lululemon outlet instyler adidas originals mulberry asics running shoes antonio brown jersey madrid jersey fitflops chrome hearts jewelry chloe handbags 2018.11.13xukaimin