In this article, I’m going to explain how to split comma-separated strings in SQL Server and also show you how to create a function in SQL Server as well as how to get comma-separated strings in the form of a SQL table.
While working with data driven application sometimes you have string data as input and you need all input data in tabular format and you need to split your input string data by any delimiter like comma(‘,’), (“https: //www.codingvila .com/”), single quotes, double quotes, etc.
Today I got the same requirement I have a comma separated string with the names of all the students and I need a SQL table for all those students to display those records within a grid. So I wrote a SQL function “split” this function splits an input string with a separator that is passed inside the function as a parameter and through this article I want to share this function with you.
demand
1) Create a function in SQL Server.
2) Split an input string with a delimiter passed inside the function as input.
3) Return a SQL Table with all that data available within the input string.
Application
Before starting the actual implementation, I just want to show you a basic syntax for creating a function in SQL Server
Syntax for creating a function in SQL Server
CREATE FUNCTION [dbo].[Name_Of_Function] -- required parameters AS BEGIN -- Your logical sql statements END
Now I’ll show you how to write a SQL function to store our requirement and spit out a comma separated string and return an 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 a select statement and get the result as a SQL table with data according to the input string.
SELECT ItemValue FROM Split('Nikunj Satasiya,Hiren Dobariya,Vivek Ghadiya,Pratik Pansuriya',',')
explanation
If you parse the SQL function created above to split an input string with a comma then I return a table variable @tbl with a column ItemValue Then after I declared a variable @xml So I created XML node about- with input data and stored in a declared variable @xml. Now, I simply add a value inside the declared table variable from the generated XML nodes and return that table variable as a SQL table.
 |
| Split function |
And finally I make a query to select data from the table and execute this function and this function is returned to the SQL table as given in the output window.
Productivity
 |
| Splitting a comma-separated string in SQL Server |
Summary
This article explains how to spit out comma-separated strings in SQL Server as well as how to create a function in SQL Server.
