1. Home
2. mssqlserver
3. Split Comma Separated String in SQL Server

Split Comma Separated String in SQL Server

Category > MSSQLSERVER || Published on : Sunday, November 29, 2020 || Views: 1418 || Split Comma Separated String in SQL Server

Here Pawan Kumar will explain how to Split Comma Separated String in SQL Server

Step 1 - Syntax to Create Function in Sql Server

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

The above SQL function will help us to archive our requirement and spit string by comma(or any other special char and return sql table

Step 2 - Below SQL query will execute this function table with select statement and get result as SQL table with data as per input string.

SELECT ItemValue FROM Split('SQL,Visual Basic,Python,Java',',')

Summary

This article we have learned how you can spit comma separated string in SQL Server as well as how to create function  in SQL Server

You might also like