Split Comma Separated String in SQL Server

watch_later Tuesday, December 4, 2018

Introduction


In this article i am going to explain how to split comma separated string in sql server and also show you how to create function in sql server as well as how you can get comma separated string in form of sql table.
Split Comma Separated String
Split Comma Separated String


While you working with data driven application sometime 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 same requirement i have comma separated string with names of all the students and i need sql table for all these students to display that records within grid. So i have wrote a sql function "Split" this function split input string with separator passed within function as parameter, and thought this article that function i wanna shere with you.

Requirement 


1) Create Function in Sql Server.
2) Split Input String with Separator that passed within function as input.
3) Return Sql Table with all these data available within input string.

Implementation


Before start our actual implementation i just wanna show you a basic syntax to create function in 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 table with select statement and get 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 analysed above created sql function to split input string with 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 declared table variable from created xml nodes and return that table variable as sql table.
Split Function
Split Function

And finally i wort query of select data from table and execute that function and this function is returned a sql table as per given in 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 string in sql server as well as how to create function  in 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.



sentiment_satisfied Emoticon