Split Comma Separated String in SQL Server

watch_later 1/17/2023
In this article, I am going to explain how to split comma-separated strings 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 strings in the form of a SQL table.
Split Comma Separated String in SQL Server

While you working with a 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 the 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 splits input string with a separator passed within the function as a parameter and through 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 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 the select statement and get the result as an SQL table with data as per the 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 then I have created XML node as <input></input> with input data and stored in declared variable @xml. Now, I simply insert a value within the declared table variable from created XML nodes and return that table variable as an SQL table.
Split Function
Split Function

And finally I wort a query for selecting data from the table and executing 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
Split Comma Separated String in SQL Server

Summary

This article explains how you can spit comma-separated strings in an SQL server as well as how to create a function in an SQL server.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon