Tag Archives: Split delimited string without function in Sql Server

How to Split a String in Sql Server without using a function

It is an usual practice to create a function to split the delimited sting in Sql Server, like the ones explained in the article Split delimited String. But if we are in a situation where you don’t have a permission to create a function, then what is the alternative to split the delimited string? This article explains how we can split a delimited string in Sql Server without needing to create a function.

Let us execute the following script to create the Player table with sample data as shown in the below image.

Table with delimited string

	Team VARCHAR(50),
	Players VARCHAR(800)
INSERT INTO Player(Team, Players)
VALUES  ('India','Sachin Tendulkar,Shewag,Javagal Srinath'),
		('Australia','Ricky Ponting,Michale Clarke'),
		('South Africa','AB Deviliers')

Now, we can write a query like below to split the comma delimited players name without using the user defined function.

SELECT Team, Members.Member.value('.','VARCHAR(8000)') Player
(--Convert delimited string to XML
 SELECT Team, CAST('<Players><Player>' 
        + REPLACE(Players, ',' , '</Player><Player>') 
	+ '</Player></Players>' AS XML) AS tempPlayer 
 FROM Player ) AS tempPlayer
 CROSS APPLY tempPlayer.nodes('/Players/Player') Members(Member)

Split string without function

STRING_SPLIT function in Sql Server 2016
How to Split comma or any other character delimited string into a Table in Sql Server