Tag Archives: Split string without function

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

Sql Server, , , , , , ,

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

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

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
FROM 
(--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)

RESULT:
Split string without function

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