Introduction
In this article, I am going to explain how to split comma separated string in an SQL server and also show you how to create a function in an SQL server as well as how you can get comma separated string in form of a SQL table.
 |
| Split Comma Separated String |
In my previous article, I explained How to Pass Output Parameter to Stored Procedure in Microsoft SQL Server and SQL Server Create and Execute Dynamic Stored Procedure and SQL Server Create and Execute Parameterized Stored Procedure From Another Stored Procedure you may like to read this article also.
While you working with data-driven application sometimes you have string data as input and you need that all input data in tabular format and you need to split your input string data by any separator like comma(','), ('/'), single quotes, double quotes and etc.
Today I got the same requirement I have a comma-separated string with names of all the students and I need an SQL table for all these students to display those records within a grid. So I have written a SQL function "Split" this function split input string with separator passed within the function as a parameter and thought this article that function I wanna share with you.
Requirement
1) Create Function in SQL Server.
2) Split Input String with Separator that passed within the function as input.
3) Return SQL Table with all these data available within the input string.
Implementation
Before starting our actual implementation I just wanna show you a basic syntax to create a function in the SQL server
Syntax to Create Function in SQL Server
CREATE FUNCTION [dbo].[Name_Of_Function] -- required parameters AS BEGIN -- Your logical sql statements END
Now I will show you how o write a SQL function to archive our requirement and spit string by comma and return SQL table.
CREATE FUNCTION [dbo].[Split] ( @InputString NVARCHAR(MAX), @Separator NVARCHAR(10) ) RETURNS @tbl TABLE ( ItemValue NVARCHAR(max) ) AS BEGIN DECLARE @xml XML; SELECT @xml = CAST('<input>' + REPLACE(@InputString, @Separator, '</input><input>') + '</input>' AS XML); INSERT INTO @tbl(ItemValue) SELECT Temp.split.value('.', 'NVARCHAR(max)') AS ItemValue FROM @xml.nodes('/input') Temp(split) RETURN END
Now we will execute this function as a table with the select statement and get the result as SQL table with data as per input string.
SELECT ItemValue FROM Split('Nikunj Satasiya,Hiren Dobariya,Vivek Ghadiya,Pratik Pansuriya',',')
Explanation
If you analyzed the above-created SQL function to split input string with a comma then I return table variable @tbl with column ItemValue and then after I have declared variable @xml and then I have created XML node as <input></input> with input data and stored in declared variable @xml. Now, I simply insert value within the declared table variable from created XML nodes and return that table variable as SQL table.
 |
| Split Function |
And finally I wort a query of select data from the table and execute that function and this function is returned to a SQL table as given in the output window.
Output
 |
| Split Comma Separated String in SQL Server |
Summary
This article explains how you can spit comma-separated string in SQL server as well as how to create a function in SQL server.
