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.
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)
STRING_SPLIT function in Sql Server 2016
How to Split comma or any other character delimited string into a Table in Sql Server