Category >
MSSQLSERVER
|| Published on :
Sunday, November 29, 2020 || Views:
1430
||
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